Q1. You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_sessions dynamic management view.
B. Query the sys.dm_exec_requests dynamic management view.
C. Create a trace in SQL Server Profiler that contains the Deadlock graph event
D. Create an extended events session to capture deadlock information.
Answer: D
Q2. You have a Microsoft SQL Azure database named DBAzure1.
You create a table in DBAzure1 by using the following script:
You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.
What should you recommend creating?
A. A CHECK constraint
B. A filtered index
C. A columnstore index
D. A UNIQUE constraint
Answer: D
Q3. You have a table named Rooms that contains three columns. You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Include the RoomName column and the Position column in the Room_IX index.
B. Create a nonclustered index for RoomName, Id, and Position.
C. Create a clustered index for Id.
D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint.
Answer: B
Q4. While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
A. 04
B. 05
C. 12
D. 14
Answer: B
Q5. You are evaluating the index design.
You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute. The solution must minimize the amount of database fragmentation.
Which line of code should you use to replace line 12 of Indexes.sql?
A. (LastName);
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);
Answer: C
Q6. You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
C. BEGIN TRANSACTION WITH MARK @transactionID
D. COMMIT TRANSACTION @transactionID
Answer: C
Q7. You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
A. A FOREIGN KEY constraint
B. A trigger
C. A user-defined data type
D. A CHECK constraint
Answer: C
Q8. You need to implement security for the restore and audit process. What should you do?
A. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions.
B. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.
C. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.
D. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.
Answer: A
Q9. You have a Microsoft SQL Azure database. You have the following stored procedure:
You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees executes.
The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?
A. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
B. Add the following line of code to line 13:
WITH (UPDLOCK)
C. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. Add the following line of code to line 08:
WITH (UPDLOCK)
Answer: D
Q10. You need to optimize the index structure that is used by the tables that support the fraud detection services.
What should you do?
A. Add a hashed nonclustered index to CreateDate.
B. Add a not hash nonclustered index to CreateDate.
C. Add a not hash clustered index on POSTransactionId and CreateDate.
D. Add a hashed clustered index on POSTransactionId and CreateDate.
Answer: A