Q1. You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs. 

What should you do? 

A. Execute sp_help 'usp_GetOrdersByProduct'. 

B. Execute sp_recompile 'usp_GetOrdersByProduct'. 

C. Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct. 

D. Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct. 

View Answer

Q2. You have a SQL Server 2012 instance that hosts a single-user database. 

The database does not contain user-created stored procedures or user-created functions. 

You need to minimize the amount of memory used for query plan caching. 

Which advanced server option should you modify? 

A. Scan for Startup Procs 

B. Enable Contained Databases 

C. Optimize for Ad hoc Workloads 

D. Allow Triggers to Fire Others 

View Answer

Q3. You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers. 

Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.) 

A. @schema varbinary(100). 

B. @items varchar(max). 

C. @schema sysname. 

D. @items varbinary(max). 

E. @items xml. 

F. @schema xml. 

View Answer

Q4. You need to modify the Orders table to store the XML data used by the retailers. 

Which statement should you execute? 

A. ALTER Orders 

ADD originalOrder XML (ValidateOrder); 

B. ALTER Orders 

ADD originalOrder XML; 

C. ALTER Orders 

ADD originalOrder varchar(max); 

D. ALTER Orders 

ADD originalOrder varbinary(max); 

View Answer

Q5. 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 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03. 

B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03. 

C. Add (UPDLOCK) to the end of line 06. 

D. Add (READPAST) to the end of line 06. 

View Answer

Q6. You create a table named Customers by using the following code segment: 

You create a non-clustered index named IX_Name on the name column. 

You write the following query to retrieve all of the customers that have a name that starts with the letters SMI: 

You discover that the query performs a table scan. 

You need to ensure that the query uses the index. 

What should you do? 

A. Replace LEFT(name,3) = 'smi' by using name like 'smi%' 

B. Replace LEFT(name,3) = 'smi' by using substring(name,l,3) = 'smi' 

C. Recreate IX_Name as a unique index 

D. Recreate IX Name as a clustered index 

View Answer

Q7. Your network contains an Active Directory domain that has two groups named Group1 and 

Group2. 

The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media. 

The SQL Server instances have a database that contains a table named Table1. 

Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2. 

You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1. 

What should you recommend? 

A. A dynamic management view 

B. Filegroups 

C. Snapshot isolation 

D. User-defined views 

View Answer

Q8. You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID. 

Which line of code should you modify in CountryFromID.sql? 

A. 04 

B. 05 

C. 06 

D. 19 

View Answer

Q9. You are creating a database that will store usernames and passwords for an application. 

You need to recommend a solution to store the passwords in the database. 

What should you recommend? 

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

A. One-way encryption 

B. Encrypting File System (EFS) 

C. Transparent Data Encryption (TDE) 

D. Reversible encryption 

View Answer

Q10. You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure. 

Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer