Your success in Microsoft 70-464 is our sole target and we develop all our 70-464 braindumps in a way that facilitates the attainment of this target. Not only is our 70-464 study material the best you can find, it is also the most detailed and the most updated. 70-464 Practice Exams for Microsoft MCSE 70-464 are written to the highest standards of technical accuracy.

Q71. You administer an instance of SQL Server 2014. 

You are tasked with tuning a common set of queries. You have the results of several test executions, along with query plans. The schema and the data for all database object(s) used remain unchanged between executions. The QueryTime column is defined as a computed column that uses the GETDATE() system function. The query plans and results are shown below: You need to make an initial diagnosis of the situation, based solely on this input 

Which two statements can you make about the performance characteristics of this query? Each correct answer presents a complete solution. Choose two. 

A. The queries would perform better if the index named AccountNumber included the Name and QueryTime column. 

B. The queries would perform worse if the index named AccountNumber included the NameColumn. 

C. The queries would perform better if the index named AccountNumber included the Name column. 

D. The object Account is a table, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount. 

E. The object Account is an indexed view, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount. 

F. The object Account is a view, joining the Account-AccountNumber and Account.PKAccount objects together. 

Answer: B,D 


Q72. Topic 8) 

You have a table named Table1. Table1 has 1 million rows. 

Table1 has a columnstore index for a column named Column1. 

You need to import data to Table1. The solution must minimize the amount of time it takes 

to import the data. 

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: 


Q73. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. 

What should you use? 

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

A. A data manipulation language (DML) trigger 

B. A DEFAULT constraint 

C. A Data Definition Language (DDL) trigger 

D. A CHECK constraint 

E. A FOREIGN KEY constraint 

Answer:

Explanation: 

http://www.techrepublic.com/blog/programming-and-development/comparing-sql-serverconstraints-and-dmltriggers/402 http://msdn.microsoft.com/en-us/library/ms178110.aspx 


Q74. You are testing disaster recovery procedures. 

When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5 Cannot find server certificate with thumbprint ' 

0x9D876A3468B911ElBA4CFCBF4724019B\\ 

Msg 3013, Level 16, State 1, Line 5 

RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore ProductsDB to another server. 

Which code segment should you execute on the other server? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q75. You create a table named Customers by using the following code segment: 

You create a non-clustered index named IX_Name on the name column. 

You write the following query to retrieve all of the customers that have a name that starts with the letters SMI: 

You discover that the query performs a table scan. You need to ensure that the query uses the index. What should you do? 

A. Replace LEFT(name,3) = 'smi' by using name like 'smi%' 

B. Replace LEFT(name,3) = 'smi' by using substring(name,l,3) = 'smi' 

C. Recreate IX_Name as a unique index 

D. Recreate IX Name as a clustered index 

Answer:


Q76. You have database objects that were created by using the following script: 

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

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

A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint. 

B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement. 

C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement. 

D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint. 

Answer:


Q77. Topic 8) 

You have a SQL Server 2012 database named Database1. Database1 has a data file named 

database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is 

1.5 GB. 

Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day. 

You need to reduce the size of the log file. The solution must ensure that you can perform 

transaction log backups in the future. Which code segment should you execute? 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: 


Q78. You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure? 

A. SAVE TRANSACTION WITH MARK @transactionID 

B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID 

C. BEGIN TRANSACTION WITH MARK @transactionID 

D. COMMIT TRANSACTION @transactionID 

Answer:


Q79. Topic 8) 

You execute the following code: 

You discover that the Customers table was created in the dbo schema. 

You need to create a code segment to move the table to another schema named 

Schema2. 

What should you create? 

To answer, drag the appropriate code segments to the correct location in the answer area. 

 (Answer choices may be used once, more than once, or not at all.) 

Answer: 


Q80. You use SQL Azure to store data used by an e-commerce application. 

You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site. 

You need to ensure that other transactions are blocked from updating product data while sp1 is executing. 

Which transaction isolation level should you use in sp1? 

A. Repeatable read 

B. Read committed 

C. Serializable 

D. Snapshot 

Answer: