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. 

View Answer

Q2. You need to recommend a solution that addresses the backup issue. The solution must minimize the amount of development effort. 

What should you include in the recommendation? 

A. Indexed views 

B. Filegroups 

C. Table partitioning 

D. Indexes 

View Answer

Q3. Your company has a SQL Azure subscription. 

You implement a database named Database1. Database1 has two tables named Table1 and Table2. 

You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. 

A user named User1 informs you that he is unable to run sp1. 

You verify that User1 has the SELECT permission on Table1 and Table2. 

You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1. 

What should you do? 

A. Change sp1 to run as the saUser. 

B. Grant User1 the EXECUTE permission on sp1. 

C. Add User1 to the db_datawriter role. 

D. Grant User1 the INSERT permission on Table2. 

View Answer

Q4. You need to add a new column named Confirmed to the Attendees table. 

The solution must meet the following requirements: 

. Have a default value of false. 

. Minimize the amount of disk space used. 

Which code block should you use? 

A. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 0; 

B. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT '1' 

C. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 1; 

D. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT ‘1’; 

View Answer

Q5. You have a SQL Server 2014 instance named SQL1. 

SQL1 creates error events in the Windows Application event log. 

You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log. 

Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.) 

A. A policy 

B. A maintenance plan 

C. An alert 

D. A job 

E. A trigger 

View Answer

Q6. 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) 

View Answer

Q7. You have a Microsoft SQL Azure database that contains a table named Customers. 

You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function. 

You need to create a query that returns a list of all the Customer names and the purchase dates. 

The solution must return only customers that have purchased an item during the last 12 months. 

What should you add to the query? 

A. OUTER JOIN 

B. CROSS JOIN 

C. CROSS APPLY 

D. OUTER APPLY 

View Answer

Q8. You are planning the ManufacturingSteps table. 

You need to define the ProductID column in the CREATE TABLE statement. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q9. You need to recommend a solution that reduces the time it takes to import the supplier data. 

What should you include in the recommendation? 

A. Enable instant file initialization. 

B. Reorganize the indexes. 

C. Disable Resource Governor. 

D. Enable Auto Update Statistics. 

View Answer

Q10. You run the following code: 

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>. 

What should you implement? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. A user-defined data type 

B. An XML index 

C. A Data Definition Language (DDL) trigger 

D. A data manipulation language (DML) trigger 

E. An XML schema collection 

View Answer