You need to modify usp_SelectEmployeesByName to support server-side paging. The solution must minimize the amount of development effort required. What should you add to usp_SelectEmployeesByName?
A. A table variable
B. The ROWNUMBER keyword
C. An OFFSET-FETCH clause
D. A recursive common table expression
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.
You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName. What should you include in the recommendation?
A. Change data capture
B. Change tracking
C. Transactional replication
D. The Distributed Transaction Coordinator (DTC)
You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schemal.Table1.
You have a stored procedure named Schemal.Proc1 that accepts an XML parameter named Param1.
You need to store validated XML data in Schemal.Table1.
The solution must ensure that only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Define an XML column in Table1 by using an XML schema collection.
B. Create an XML schema collection in the database from the text file.
C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
D. use the modify method to insert the XML schema into each row of the XML column in Table1.
Question : 20
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 frag- mentation.Which line of code should you use to replace line 12 of Indexes.sql?
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);
Answer : C
Question : 19
You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously.You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures.What should you update?
A. Usp_UpdateSpeakerName to use the NOLOCK query hint
B. Usp_UpdateSpeakerName to use snapshot isolation
C. Usp_SelectSpeakersByName to use the NOLOCK query hint
D. Usp_SelectSpeakersByName to use snapshot isolation
Answer : C
Question : 18
Developers report that usp_UpdateSessionRoom periodically returns error 3960.You need to prevent the error from occurring.The solution must ensure that the stored procedure returns the original values to all of the updated rows.What should you configure in Procedures.sql?
A. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
C. Move the SELECT statement at line 49 to line 57.
D. Move the SET statement at line 46 to line 53.
Answer : A
Question : 17
You need to create the object used by the parameter of usp_InsertSessions.Which statement should you use?
A. CREATE XML SCHEMA COLLECTION SessionDataTable
B. CREATE TYPE SessionDataTable AS Table
C. CREATE SCHEMA SessionDataTable
D. CREATE TABLE SessionDataTable
Answer : B
Question : 12
You execute usp_TestSpeakers.You discover that usp_SelectSpeakersByName uses inefficient execution plans.You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used.What should you add at line 30 of Procedures.sql?
A. OPTION (FORCESCAN)
B. OPTION (FORCESEEK)
C. OPTION (OPTIMIZE FOR UNKNOWN)
D. OPTION (OPTIMIZE FOR (@LastName= ‘Anderson’))
Answer : C
Question : 11
You execute IndexManagement.sql and you receive the following error message:”Msg 512, Level 16, State 1, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, <, <= ,>, > = or when the subquery is used as an expression.”You need to ensure that IndexManagement.sql executes properly.Which WHILE statement should you use at line 18?
A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)
Answer : B
Question : 10
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?
Answer : D
Question : 9
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 10 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’,
Answer : C
Question : 8
You have a table named Table1 that stores customer data. Each customer has a credit limit that can only be discovered by querying multiple tables. You need to ensure that the value of the credit limit is returned by executing a query on Table1.What should you create?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function
Answer : C
Question : 7
Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers. You add an additional server named SQL2 that runs SQL Server 2012. You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add SQL2 as a Distributor.
B. Add the Customers table to SQL2.
C. Add SQL2 as a linked server.
D. Create the view on SQL1.
E. Remove the Customers table from SQL1.
F. Create the view on SQL2.
Question : 6
You need to ensure that a new execution plan is used by usp_GetOrdersByProduct eachtime the stored procedure runs.What should you do?
A.Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.
B.Execute sp_recompile ‘usp_GetOrdersByProduct’.
C.Execute sp_help ‘usp_GetOrdersByProduct’.
D.Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
Answer : D
Question : 5
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.
Answer : C
Search Words: 70-464 Exam real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-464 Exam discount coupon code available.