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. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct. 

C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct. 

D. Execute sp_recompile usp.GetOrdersByProduct'. 

View Answer

Q2. You need to recommend a solution to minimize the amount of time it takes to execute USP_1. 

With what should you recommend replacing Table1? 

A. A view 

B. A temporary table 

C. A table variable 

D. A function 

View Answer

Q3. You need to implement a change to usp_ExportOpenings that meets the integration requirements. 

What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?) 

A. To the end of line 04, add [Opening]. 

B. To the end of line 05, add [Opening! title]. 

C. To line 10, add FOR XML RAW. 

D. To line 10, add FOR XMLEXPLICIT. 

E. To line 10, add FOR XML AUTO. 

F. To the end of line 04, add [Opening!ELEMENT]. 

G. To the end of line 06, add [Opening!salary!ELEMENT]. 

H. To the end of line 05, add [Opening!title!ELEMENT]. 

I. To the end of line 06, add [Opening! salary]. 

View Answer

Q4. You need to convert the functionality of Legacy.sql to use a stored procedure. Which code segment should the stored procedure contain? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q5. You have an application that uses a view to access data from multiple tables. 

You need to ensure that you can insert rows into the underlying tables by using the view. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Define the view by using the SCHEMABINDING option. 

C. Define the view by using the CHECK option. 

D. Materialize the view. 

View Answer

Q6. You need to implement a solution that solves the performance issues of usp_GetOrdersAndItems. 

Which statements should you execute? 

A. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate, Amount) 

B. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate) INCLUDE(Amount) WHERE ShipDate IS NULL 

C. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate, Amount) WHERE ShipDate IS NULL 

D. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate) INCLUDE( Amount) 

View Answer

Q7. You are the new database administrator for a SQL Server 2014 instance. 

You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring. 

You want to set up monitoring to see if query performance is being affected. 

You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed. 

What should you do? 

A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database. 

B. Use the missing_column_statistics extended event. 

C. Query the sys.statistics system view to see all cases where the statistics were last needed. 

D. Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical. 

View Answer

Q8. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. 

The following is the relevant portion of the execution plan for the query: 

You need to create the missing index. 

Which code segment should you execute? 

A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3 

B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) 

C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4) 

D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4) 

View Answer

Q9. You deploy a database by using SQL Server 2014. The database contains a table named Table1. 

You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required. 

What should you recommend? 

A. Change data capture 

B. Statistics 

C. A trigger 

D. Master Data Services 

View Answer

Q10. You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: "Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 

Cannot insert the value NULL into column 'InvoiceDate', table 'DB1.Accounting.Invoices' column does not allow nulls. INSERT fails." 

You need to modify usp_InsertInvoice.sql to resolve the error. 

How should you modify the INSERT statement? 

A. InvoiceDate varchar(l00) 'InvoiceDate', 

B. InvoiceDate varchar(100) 'Customer/InvoiceDate', ' 

C. InvoiceDate date '@InvoiceDate', 

D. InvoiceDate date 'Customer/@InvoiceDate', 

View Answer