Skip to content

Rebirth 70-464 Exam Study Guides With New Update Exam Questions

Exambible offers free demo for 70-464 exam. “Developing Microsoft SQL Server 2012 Databases”, also known as 70-464 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-464 exam, will help you answer those questions. The 70-464 Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-464 exams and revised by experts!

2018 NEW RECOMMEND

Free VCE & PDF File for Microsoft 70-464 Real Exam
(Full Version!)

Pass on Your First TRY 100% Money Back Guarantee Realistic Practice Exam Questions

Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on:
https://www.certleader.com/70-464-dumps.html

Q91. 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:

Topic 3, Scenario 3 

Application Information 

You have two servers named SQL1 and SQL2. SQL1 has SQL Server 2012 Enterprise installed. SQL2 has SQL Server 2008 Standard installed. 

You have an application that is used to manage employees and office space. Users report that the application has many errors and is very slow. 

You are updating the application to resolve the issues. You plan to create a new database on SQL1 to support the application. The script that you plan to use to create the tables for the new database is shown in Tables.sql. The script that you plan to use to create the stored procedures for the new database is shown in StoredProcedures.sql. The script that you plan to use to create the indexes for the new database is shown in Indexes.sql. A database named DB2 resides on SQL2. DB2 has a table named EmployeeAudit that will audit changes to a table named Employees. 

A stored procedure named usp_UpdateEmployeeName will be executed only by other stored procedures. The stored procedures executing usp_UpdateEmp!oyeeName will always handle transactions. 

A stored procedure named usp_SelectEmployeesByName will be used to retrieve the names of employees. Usp_SelectEmployeesByName can read uncommitted data. 

A stored procedure named usp_GetFutureOfficeAssignments will be used to retrieve office assignments that will occur in the future. 

StoredProcedures.sql 

Indexes.sql Tables.sql 

Q92. 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:

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. 

Q93. 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 WITH (NOLOCK) to the end of line 47. 

B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44. 

C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44. 

D. Add WITH (READPAST) to the end of line 47. 

Answer:

Q94. 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:

Q95. You plan to create a new table that has the following requirements: 

… 

Uses a GUID data type as the primary key. Uses a clustered index as the primary key. Minimizes fragmentation. 

You need to recommend which option to include in the CREATE statement. 

Which option should you include? 

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

A. NEWID 

B. @@IDENTITY 

C. NEWSEQUENTIALID 

D. IDENTITY 

Answer:

Q96. 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:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms180800.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx 

Q97. You plan to create a new column in a table. The column must meet the following requirements: 

. Be able to store images that are larger than 1 MB each. 

. Be able to access the images from Microsoft .NET Framework applications. 

You need to recommend which data type must be used in the column. 

Which data type should you recommend? 

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

A. nvarchar 

B. varbinary 

C. image 

D. FileStream 

Answer:

Q98. Topic 8) 

You have a SQL Server 2012 database named database1. 

Users report that queries that usually take less than one second to execute, take more than 

30 seconds to execute. 

You need to view the server resource consumption when the queries are executed. 

What should you do? 

To answer, move the appropriate actions from the list of actions to the answer area and 

arrange them in the correct order. 

Answer: 

Q99. You need to implement a solution that addresses the index monitoring requirements. 

What should you do? 

A. Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database. 

B. Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group. 

C. Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters. 

D. Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database. 

Answer:

Q100. You need to implement a solution that meets the security requirements. Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: