70-464 Exam - Real Exam Questions

Practice Our 70-464 Exam Exam Questions and Pass Your Exam Easily.


70-464 Exam

If you are looking for free 70-464 dumps than here we have some sample question answers available. You can prepare from our Microsoft 70-464 exam questions notes and prepare exam with this practice test. Check below our updated 70-464 Study Material.

Developing Microsoft SQL Server Databases

Skills measured in this exam are

Implement database objects (30-35%)
Implement programming objects (20-25%)
Design database objects (20-25%)
Optimize and troubleshoot queries (20-25%)

Why Should Take this exam????

This exam is intended for database professionals who build and implement databases across organizations and who ensure high levels of data availability. Their responsibilities include creating database files, data types, and tables; planning, creating, and optimizing indexes; ensuring data integrity; implementing views, stored procedures, and functions; and managing transactions and locks. Register for Exam at Developing Microsoft SQL Server Databases 
For Information Exam 70-464 wiki
For Information Connect with us on @MSLearning
For Information http://www.microsoft.com/learning/en-us/certification-exams.aspx Learning source on Twitter at https://twitter.com/MSLearning

Free Download 70-464 Exam Questions 


You can prepare from these 70-464 exam questions or get help from 70-464 dumps for better preparation.

70-464 Dumps


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

Answer: C


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.

Answer: D


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)

Answer: D


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.

Answer: A,B,D


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?

A. (LastName);
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);

Answer : C


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


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


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


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


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


page 1 from 2 next page »