Q1. Which code segment should you use to define the ProductDetails column? 

A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL 

B. ProductDetails xml NULL 

C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL 

D. ProductDetails varchar(MAX) NULL 

View Answer

Q2. You have a SQL Server 2012 database named Database1. 

Database1 has a table named Customers. Customers contains more than 1 million rows. 

The database has a stored procedure that was created by using the following script: 

You need to ensure that up_customers returns rows when the following statement is executed: 

EXECUTE up_customers'1,2,3,4,5' 

What should you do? 

A. Update @CustcmerTypelist to use the int data type. 

B. Convert @CustomerTypeList to a table variable. 

C. Convert @CustomerTypeList to an XML variable. 

D. Update @CustomerTypeList to use the XML data type. 

View Answer

Q3. You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. 

What should you add to usp.GetOrdersAndItems? 

A. Add WITH (NOLOCK) to the end of line 47. 

B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44. 

C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44. 

D. Add WITH (READPAST) to the end of line 47. 

View Answer

Q4. You need to identify the cause of the page split issues. 

Which SQL Server feature should you use? 

A. DBCC REINDEX 

B. SQL Server Profiler 

C. Extended Events 

D. DBCC TRACEOFF 

View Answer

Q5. You need to modify Production.ProductDetails_Insert to comply with the application requirements. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q6. Topic 8) 

You are planning two stored procedures named SProc1 and SProc2. 

You identify the following requirements: 

. SProc1 must return a table. 

. SProc2 must return a scalar value. You need to identify which option must be implemented for each stored procedure to return the desired data. 

Which options should you identify? 

To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.) 

View Answer

Q7. You use SQL Server 2012 to maintain the data used by the applications at your company. 

You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.) 

... 

You need to ensure that Table1 contains a column named UserName. The UserName column will: 

Store string values in any language. 

Accept a maximum of 200 characters. 

Be case-insensitive and accent-insensitive. 

Which code segment should you add at line 03? 

A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL, 

B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL, 

C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL, 

D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL, 

View Answer

Q8. 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

Q9. Topic 8) 

You run the following code segment: 

After you add 10,000 rows to Customers, you discover that the index is fragmented. 

You need to defragment the index in the least amount of time. 

Which code segment should you execute? 

To answer, drag the appropriate value to the correct location in the code segment in the 

answer area. (Answer choices may be used once, more than once, or not at all.) 

View Answer

Q10. Topic 8) 

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements: 

. SProc1 must return a table. 

. SProc2 must return a status code. 

You need to identify which options must be implemented to meet each stored procedure requirement. 

Which options should you identify? 

To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.) 

View Answer