Looking to become a certified Microsoft professional? Would you like to reduce or minimize your Microsoft 70-464 certification cost? Do you want to pass all of the Microsoft certification? If you answered YES, then look no further. Lead2pass offers you the best Microsoft exam certification test questions which cover all core topics and certification requirements.

QUESTION 171
You have a SQL Server 2012 database named Database1.
You execute the following code:

wps8994.tmp_thumb[2]

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

wpsCCDC.tmp_thumb

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

Answer: A

QUESTION 172
You have database objects that were created by using the following script:

wps27C8.tmp_thumb[2]

The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.
The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit button.)

wps6739.tmp_thumb

You need to ensure that usp_GetCustomersByDate completes as quickly as possible.
What should you do?

A.    Modify the stored procedure to include the OPTIMIZEFOR UNKNOWN query hint.
B.    Execute the sp_recompile ‘dbo.GetCustomersByDate’ statement.
C.    Execute the ALTER INDEXIX_Cust:omers_CreationDateWITH REBUILD statement.
D.    Modify the stored procedure to include the OPTIMIZE FOR(‘1/1/2008’) query hint.

Answer: A

QUESTION 173
Drag and Drop Question
You create a table that contains the following script:

wps974E.tmp_thumb

You need to prevent duplicate values in the EmployeeID field.
Which five code segments should you use?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

wpsC706.tmp_thumb

Answer:

wpsECBF.tmp_thumb

QUESTION 174
You have a SQL Server 2014 instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements:
– Remain online during the defragmentation.
– Update distribution statistics.
– Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

A.    DBCC DBREINDEX
B.    REORGANIZE
C.    REBUILD
D.    DBCC INDEXDEFRAG

Answer: B

QUESTION 175
Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Serverscontains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 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 Server5.
B.    Add Server9 as a linked server.
C.    Create the view on Server9.
D.    Add the Customers table to Server9.
E.    Add Server9 as a Distributor.
F.    Remove the Customers table from Server5.

Answer: ABCD

QUESTION 176
Drag and Drop Question
You have two existing tables, one named COUNTRY and the other named STATES.
The tables are defined as follows:

wps340D.tmp_thumb[2]

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

wps7EB4.tmp_thumb

Answer:

wpsA892.tmp_thumb

QUESTION 177
You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client.
What should you do?

wpsEAEF.tmp_thumb[2]

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

Answer: A
Explanation:
* SET XACT_ABORT
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

QUESTION 178
You have the following query on a disk-based table:

wps3A09.tmp_thumb[2]

You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

wps7A93.tmp_thumb[2]

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

wpsC192.tmp_thumb[2]

You need to reduce the amount of time it takes to complete the query.
You must achieve this goal as quickly as possible.
What should you do?

A.    Reorganize the index.
B.    Update statistics.
C.    Create an index on LastName.
D.    Rebuild the index.

Answer: C

QUESTION 179
Drag and Drop Question
You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:

wpsE9A.tmp_thumb[2]

Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.
2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3. Any error that occurs during the course of the trigger’s execution must prevent the changes from happening.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

wps5E40.tmp_thumb

Answer:

wps8AFB.tmp_thumb

QUESTION 180
Drag and Drop Question
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day’s activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
– Both processes must never use more than 50 percent of the CPU at any one time.
– The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
– The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
– Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

wpsD277.tmp_thumb

Answer:
wpsFE97.tmp_thumb

Choose Lead2pass you will get:

Most updated collection of Microsoft 70-464 Exam Questions;
Prepare according to the latest Microsoft 70-464 Exam Syllabus;
Product is instantly available for download and use;
24×7 Customer Support on case of problemwith the product.

http://www.lead2pass.com/70-464.html