Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 161:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.

  You have a database named dbReporting. Users run a large number of read-only ad hoc queries against the database. The application and all ad hoc queries use default database transaction isolation levels. You set the value of the

  READ_COMMITTED_SNAPSHOT database option to ON.

  You have an application that updates 10 tables sequentially and modifies a large volume of records in a single transaction. The updates are isolated from each other.

  Users report an error which indicates that the version store us full.

  You need to reduce the number of occurrences of the error.

  Solution: You increase the maximum database size for the dbReporting database.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 162:

  HOTSPOT

  You have tables with the following requirements:

  Some records in the table named OrderLines do not exist in the table named Order.

  The column named OrderNumber must be a unique value in the Order table.

  The OrderNumber column on the OrderLines table allows null values.

  The OrderNumber column on the OrderLines table must be validated with the Order table.

  Must not allow a new record in the OrderLines table that does not match a record in the Order table.

  How should you complete the statements? To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: PRIMARY KEY

  Box 2: NOCHECK

  Need NOCHECK as some records in the table named OrderLines do not exist in the table named Order.

  Box 3: FOREIGN KEY

  FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.

  References:

  https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/

• Question 163:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question os independent of the other questions in this series. Information and details provided in a question apply only to that question.

  You have a database named DB1. There is no memory-optimized filegroup in the database. You run the following query:

  

  The following image displays the execution plan the query optimizer generates for this query:

  

  Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk. You need to create an index on the Person.Person table that meets the following requirements:

  1.

  All users must be able to benefit from the index.

  2.

  FirstName must be added to the index as an included column.

  What should you do?

  A. Create a clustered index on the table.
  B. Create a nonclustered index on the table.
  C. Create a nonclustered filtered index on the table.
  D. Create a clustered columnstore index on the table.
  E. Create a nonclustered columnstore index on the table.
  F. Create a hash index on the table.
  B. Create a nonclustered index on the table.

  ---

  By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:

  They can be data types not allowed as index key columns.

  They are not considered by the Database Engine when calculating the number of index key columns or index key size.

• Question 164:

  You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the

  quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.

  Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID, Qtysold, Totprodsales, and Datesold columns.

  You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.

  What should you include in your solution?

  A. Create a unique constraint.
  B. Create a foreign key constraint to the StoreID column in each of the store tables.
  C. Create a rule and bind it to the StoreID column.
  D. Create a check constraint.
  E. Create a table-valued user-defined function.
  A. Create a unique constraint.

  ---

  You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

  Incorrect Answers:

  D: CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-2017

• Question 165:

  HOTSPOT

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

  

  You must modify the ProductReview Table to meet the following requirements:

  * The table must reference the ProductID column in the Product table.

  * Existing records in the ProductReview table must not be validated with the Product table.

  * Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

  * Changes to records in the Product table must propagate to the ProductReview table.

  You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

  You must modify the Orders table to meet the following requirements:

  * Create new rows in the table without granting INSERT permissions to the table.

  * Notify the sales person who places an order whether or not the order was completed.

  You must add the following constraints to the SalesHistory table:

  * a constraint on the SaleID column that allows the field to be used as a record identifier

  * a constant that uses the ProductID column to reference the Product column of the ProductTypes table

  * a constraint on the CategoryID column that allows one row with a null value in the column

  * a constraint that limits the SalePrice column to values greater than four

  Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

  You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

  * The table must hold 10 million unique sales orders.

  * The table must use checkpoints to minimize I/O operations and must not use transaction logging.

  * Data loss is acceptable.

  Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

  How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

• Question 166:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

  You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data for the last order that the customer placed.

  You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

  You need to implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:

  What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Using TRY...CATCH inTransact-SQL

  Errors in Transact-SQL code can be processed by using a TRY-

• Question 167:

  You have a view that includes an aggregate.

  You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

  You need to ensure that you can update the view.

  What should you create?

  A. table-valued function
  B. a schema-bound view
  C. a partitioned view
  D. a DML trigger
  D. a DML trigger

• Question 168:

  You are monitoring a Microsoft Azure SQL Database.

  The database is experiencing high CPU consumption.

  You need to determine which query uses the most cumulative CPU.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all. You may need to drag

  the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1: sys.dm_exec_query_stats sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server. Box 2: highest_cpu_queries.total_worker_time DESC Sort ontotal_worker_time column

  Example:

  The following example returns information about the top five queries ranked by average CPU time.

  Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.

  USE AdventureWorks2012;

  GO

  SELECT TOP 5 query_stats.query_hash AS "Query Hash",

  SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

  MIN(query_stats.statement_text) AS "Statement Text"

  FROM

  (SELECT QS.*,

  SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

  ((CASE statement_end_offset

  WHEN -1 THEN DATALENGTH(ST.text)

  ELSE QS.statement_end_offset END

  -QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;

  References:

  https://msdn.microsoft.com/en-us/library/ms189741.aspx

• Question 169:

  You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, DELETE and BACKUP DATABASE statements. You have the following requirements:

  You must run all the stored procedures within the same transaction.

  You must automatically start a transaction when stored procedures include DML statements.

  You must not automatically start a transaction when stored procedures include DDL statements.

  You need to run all three stored procedures.

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.

  Select and Place:

  

  

  ---

  Note:

  Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement,which returns the connection to autocommit mode. In autocommit mode, allindividual statements are committed if

  they complete successfully.

  When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

  

  Note 2: XACT_STATE returns the following values.

  1 The currentrequest has an active user transaction. The request can perform any actions, including writing data and committing the transaction. The transaction is committable.

  -1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.the transaction is uncommittable and should be rolled back.

  0 There is no active user transaction for the current request. Acommit or rollback operation would generate an error.

  References:

  https://technet.microsoft.com/en-us/library/ms187807(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms189797(v=sql.110).aspx

• Question 170:

  You have a relational data warehouse that contains 1 TB of data.

  You have a stored procedure named usp_sp1 that generated an HTML fragment. The HTML fragment contains color and font style.

  You need to return the HTML fragment.

  What should you do?

  A. Use the NOLOCK option.
  B. Execute the DBCC UPDATEUSAGE statement.
  C. Use the max worker threads option.
  D. Use a table-valued parameter.
  E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
  F. Set SET XACT_ABORT to ON.
  G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
  H. Use the OUTPUT parameters.
  G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.

  ---

  A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. References: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-2017