Online Access Free 070-464 Practice Test

You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?

• A.Repeatable read
• B.Read committed
• C.Snapshot
• D.Serializable

You run the following code:

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.

• A.A data manipulation language (DML) trigger
• B.An XML schema collection
• C.An XML index
• D.A user-defined data type
• E.A Data Definition Language (DDL) trigger

You have a SQL Server 2012 instance.
You plan to create an application that uses spatial data.
You need to create an object that will support the representation of the surface area of all the oceans.
Which code segment should you use?

• A.Option B
• B.Option A
• C.Option D
• D.Option C

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

You discover that the stored procedure periodically fails to update HR.Employees.
You need to ensure that HR.Employees is always updated when up_employees 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 05:
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• B.Add the following line of code to line 08:
  WITH (UPDLOCK)
• C.Add the following line of code to line 13:
  WITH (UPDLOCK)
• D.Add the following line of code to line 05:
  SET TRANSACTION ISOLATION LEVEL SNAPSHOT

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.Use the WITH (INDEX(Room_IX),NOLOCK) query hint.
• D.Create a clustered index for Id.