Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 51:

  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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index. Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

  

  You need to resolve the performance issue. Solution: You drop the nonclustered index. Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

• Question 52:

  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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index. Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

  

  You need to resolve the performance issue. Solution: You defragment both indexes. Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

• Question 53:

  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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.

  Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

  

  You need to resolve the performance issue.

  Solution: You update statistics for the nonclustered index.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: A

  We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

• Question 54:

  You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.

  The environment is configured with the following properties:

  The database is configured with the default isolation setting.

  The application and process use the default transaction handling.

  You observe the application cannot access any rows that are in use by the process.

  You have the following requirements:

  Ensure the application is not blocked by the process.

  Ensure the application has a consistent view of the data

  Ensure the application does not read dirty data.

  You need to resolve the issue and meet the requirements with the least amount of administrative effort.

  What should you do?

  A. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.

  B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.

  C. Enable the application for the WITH (NOLOCK) hint.

  D. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.

  Correct Answer: B

  Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).

  READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still

  modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

  Incorrect Answers:

  A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

  References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

• Question 55:

  You run the following Transact-SQL following statement:

  

  Customer records may be inserted individually or in bulk from an application.

  You observe that the application attempts to insert duplicate records.

  You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.

  Which Transact-SQL statement should you run?

  A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = OFF)

  B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)

  C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY =OFF)

  D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)

  E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

  Correct Answer: B

  IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created

  or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

  Incorrect Answers:

  ONLINE = { ON | OFF } specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017

• Question 56:

  You suspect deadlocks on a database.

  Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. 1204

  B. 1211

  C. 1222

  D. 2528

  E. 3205

  Correct Answer: AC

  Trace flag 1204 returns the resources and types of locks participating in a deadlock and also the current command affected. Trace flag 1222 returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

  References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

• Question 57:

  You have the following stored procedure that is called by other stored procedures and applications:

  

  You need to modify the stored procedure to meet the following requirements:

  Always return a value to the caller.

  Return 0 if @Status is NULL.

  Callers must be able to use @Status as a variable.

  Which two actions should you perform? Each correct answer presents part of the solution.

  NOTE: Each correct selection is worth one point.

  A. Replace NULL values with 0. Add a PRINT statement to return @Status.

  B. Add a RETURN statement.

  C. Replace NULL values with 0. Add an output parameter to return @Status.

  D. Replace NULL values with 0. Add a SELECT statement to return @Status.

  E. Add a PRINT statement.

  F. Add a SELECT statement to return @Status.

  G. Add an output parameter to return @Status.

  Correct Answer: BC

  There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

• Question 58:

  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 is independent of the other questions in the series.

  Information and details provided in a question apply only to that question.

  You are developing an application to track customer sales.

  You need to create an object that meets to following requirements:

  Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.

  Run written a transaction and roll back if a failure occurs.

  Run when a table is created or modified.

  What should you create?

  A. extended procedure

  B. CLR procedure

  C. user-defined procedure

  D. DML trigger

  E. DDL trigger

  F. scalar-valued function

  G. table-valued function

  Correct Answer: B

  The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration

• Question 59:

  You are creating the following two stored procedures: A natively-compiled stored procedure An interpreted stored procedure that accesses both disk-based and memory-optimized tables

  Both stored procedures run within transactions.

  You need to ensure that cross-container transactions are possible.

  Which setting or option should you use?

  A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection

  B. the SERIALIZABLE table hint on disk-based tables

  C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database

  D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

  Correct Answer: C

  Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.

  Incorrect Answers:

  B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

• Question 60:

  You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB.

  The table is updated thousands of times a day, but data is not deleted.

  The SELECT statements vary in the number of columns used and the amount of rows retrieved.

  You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.

  Which indexing strategy should you use?

  A. a nonclustered index for each column in the table

  B. a clustered columnstore index for the table

  C. a hash index for the table

  D. a clustered index for the table and nonclustered indexes for nonkey columns

  Correct Answer: B

  Columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over

  traditional row-oriented storage.

  A clustered columnstore index is the physical storage for the entire table.

  Generally, you should define the clustered index key with as few columns as possible.

  A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes should be

  designed to improve the performance of frequently used queries that are not covered by the clustered index.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017