Download New Updated (June) Microsoft 70-464 Practice Test 121-130

June 7, 2015

70-464 Practice Tests (June)

, , , , ,

Ensurepass

 

QUESTION 121

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

A Data Definition Language (DDL) trigger

B.

A DEFAULT constraint

C.

A CHECK constraint

D.

A FOREIGN KEY constraint

E.

A data manipulation language (DML) trigger

 

Correct Answer: E

Explanation:

http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402

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

 

 

QUESTION 122

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1. You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1. You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

 

A.

Define an XML column in Table1 by using an XML schema collection.

B.

Create an XML schema collection in the database from the text file.

C.

Declare Param1 var1 as type XML and associate the variable to the XML schema collection.

D.

use the modify method to insert the XML schema into each row of the XML column in Table1.

 

Correct Answer: ABD

Explanation:

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

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

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

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

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

 

 

 

QUESTION 123

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that many page splits occur when records are inserted or updated in the table. You need to minimize the number of page splits. What should you set from the index options?

 

A.

FILLFACTOR = 0

B.

STATISTICS_NORECOMPUTE = OFF

C.

STATISTICS_NORECOMPUTE = ON

D.

FILLFACTOR = 80

 

Correct Answer: D

Explanation:

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

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

 

 

QUESTION 124

You have a SQL Server 2012 database named Database1.

 

You execute the following code:

 

clip_image002

 

You insert 3 million rows into Sales.

 

You need to reduce the amount of time it takes to execute Proc1.

 

What should you do?

 

clip_image004

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 125

DRAG DROP

You execute the following code:

 

clip_image006

 

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

 

clip_image008

 

Correct Answer:

clip_image010

 

 

 

 

 

 

QUESTION 126

DRAG DROP

You plan to deploy two stored procedures named SP1 and SP2 that read data from the database.

 

Your company identifies the following requirements for each stored procedure:

 

clip_image012SP1 must allow dirty reads.

clip_image012[1]SP2 must place range locks on the data to ensure read consistency.

 

You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.

 

Which isolation level should you identify?

 

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than o
nce, or not at all.)

 

clip_image014

 

Correct Answer:

clip_image016

 

 

QUESTION 127

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

 

clip_image018

 

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

 

clip_image019

 

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.

 

Correct Answer: A

 

 

 

QUESTION 128

You have a database named DB1.

 

You plan to configure change data capture on the existing tables in DB1.

 

The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)

 

clip_image021

 

You need to minimize disk contention caused by change data capture.

 

What should you do?

 

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

 

A.

Increase the autogrowth value of the database file.

B.

Set the database recovery model to simple.

C.

Increase the autogrowth value of the log file.

D.

Configure change data capture to use to a secondary filegroup.

 

Correct Answer: D

 

 

 

QUESTION 129

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.

 

You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:

 

clip_image012[2]Provide a representation of the deadlock in XML format.

clip_image012[3]Minimize the impact on the server.

 

What should you create?

 

A.

A SQL Server Profiler trace

B.

A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views

C.

A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

D.

A script that enables trace flags

 

Correct Answer: A

 

 

QUESTION 130

You have a database named Database1.

 

You execute the following code:

 

clip_image023

 

You have the following query. (Line numbers are included for reference only.)

 

clip_image025

 

Users report that the query takes a long time to complete.

 

You create a full-text index for the Notes column.

 

You need to recommend changes to the query to reduce the amount of time it takes for the query to complete.

 

Which code segment should you use to replace line 03?

 

A.

WHERE FREETEXT(notes, ‘%call%’) AND

B.

INNER JOIN FREETEXTTABLE(dbo.table1, notes, ‘call’) AS t2 ON dbo.table1.ID = t2.key WHERE

C.

WHERE CONTAINS(notes, ‘call*’) AND

D.

WHERE CONTAINS(notes,*%call%’> AND

 

Correct Answer: A

Free VCE & PDF File for Microsoft 70-464 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…

Related Posts