Online Access Free 070-762 Practice Test

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You add a unique clustered index to the Employees table.
Does the solution meet the goal?

• A.No
• B.Yes

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 that contains a table named Employees. The table stores information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data recorded by the audit operations.
Solution: You implement a user-defined function on the Employees table.
Does the solution meet the goal?

• A.No
• B.Yes

You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.

Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.

Correct Answer:

Explanation

Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables.
Module 2: Natively compiled stored procedure
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?v

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 that contains a table named Employees. The table stored information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data recorded by the audit operations.
Solution: You implement a stored procedure on the Employees table.
Does the solution meet the goal?

• A.No
• B.Yes

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.

Correct Answer:

Explanation

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, all individual 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 current request 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. A commit 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