Free Microsoft 70-464 Exam Dumps Questions & Answers

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.Create the view on SQL2.
• B.Add SQL2 as a linked server.
• C.Remove the Customers table from SQL1.
• D.Create the view on SQL1.
• E.Add SQL2 as a Distributor.
• F.Add the Customers table to SQL2.

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 READ COMMITTED to line 44.
• B.Add WITH (READPAST) to the end of line 47.
• C.Add WITH (NOLOCK) to the end of line 47.
• D.Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.

Correct Answer:

Explanation:
Note:
* sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
* sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

You have a SQL Azure database named Database1. You need to design the schema for a table named table1.
Table1 will have less than one million rows. Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.

Correct Answer:

Explanation:
http://msdn.microsoft.com/en-US/library/ms187752.aspx

You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?

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