70-464 Exam Questions - Online Test

70-464 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Actual of 70-464 exam guide materials and training materials for Microsoft certification for IT candidates, Real Success Guaranteed with Updated 70-464 pdf dumps vce Materials. 100% PASS Developing Microsoft SQL Server 2012 Databases exam Today!

Q11. You need to implement a solution that meets the site requirements. 

What should you implement? 

A. A non-indexed view on Server1 

B. A non-indexed view on Server2 

C. A distributed view on Server1 

D. A distributed view on Server2 

Answer: C 

Q12. Topic 1) 

You need to build a stored procedure that amortizes the invoice amount. Which code segment should you use to create the stored procedure? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order. 

Answer: 

Q13. 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, 

Answer: D 

161. You plan to modify a procedure that contains hundreds of lines of code. 

The modification must support the following guidelines: 

. Use only tables that are not persistent in the database. 

. Minimize the amount of time required to execute and recompile procedures. 

You need to identify which type of table must be used to support the planned modification. 

Which type of table should you identify? 

A. A system table 

B. A partitioned table 

C. A table variable 

D. A temporary table 

Answer: C 

Q14. You need to implement a solution that addresses the upload requirements. Which code segment should you use to implement the Conversions assembly? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 

Q15. You need to create the InvoiceStatus table in DB1. 

How should you define the InvoiceID column in the CREATE TABLE statement? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 

Q16. You have a table named Rooms that contains three columns. You execute the following query: 

You discover the execution plan shown in the exhibit. (Click the Exhibit button.) 

You need to recommend a solution to reduce the amount of time it takes to execute the query. 

What should you do? 

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

A. Include the RoomName column and the Position column in the Room_IX index. 

B. Create a nonclustered index for RoomName, Id, and Position. 

C. Create a clustered index for Id. 

D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint. 

Answer: B 

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

Q18. You execute the following code: 

You have a stored procedure that includes the following SELECT statement: 

You need to create a covering index on UserInfo. Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 

Q19. While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek. 

You need to minimize the amount of time it takes to execute usp_GetFutureSessions. 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

Answer: B,E 

Explanation: Future delivery dates. 

Q20. You have a Microsoft SQL Azure database. You have the following stored procedure: 

You discover that the stored procedure periodically fails to update Person.Contact. 

You need to ensure that Person.Contact is always updated when UpdateContact 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 12: 

WITH (UPDLOCK) 

B. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

C. Add the following line of code to line 08: 

WITH (UPDLOCK) 

D. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

Answer: C 

Explanation: * Overall, you should use UPDLOCK when you read a value that you plan to update later in the same transaction to prevent the value from changing. 

* UPDLOCK Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead. When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.