Download New Updated (June) Microsoft 70-469 Practice Test Topic 13, Mix Questions Set 2 part03

Ensurepass

QUESTION 195  (Topic 13)

 

You need to identify which long running transactions use an index.

 

 

Which dynamic management view should you use?

 

A.

sys.dm_exec_query_optimizer_info

B.

sys.dm_exec_connections

C.

sys.dm_exec_query_stats

D.

sys.dm_exec_sessions

 

Answer: A

 

 

QUESTION 196

DRAG DROP  (Topic 13)

 

You execute the following code:

 

clip_image002

 

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_image004

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 197  (Topic 13)

 

You have a Microsoft SQL Azure database named DBAzure1.

 

You create a table in DBAzure1 by using the following script:

 

 

 

clip_image006

 

You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.

 

What sho
uld you recommend creating?

 

A.

A CHECK constraint

B.

A filtered index

C.

A columnstore index

D.

A UNIQUE constraint

 

Answer: D

 

 

QUESTION 198

DRAG DROP  (Topic 13)

 

You are designing two stored procedures named Procedure1 and Procedure2.

 

You identify the following requirements:

 

Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.

Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.

 

You need to identify the appropriate technology for each stored procedure.

 

Which technologies should you identify?

 

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

 

 

 

clip_image008

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 199  (Topic 13)

 

You have a server that has SQL Server 2012 installed.

 

You need to identify which parallel execution plans are running in serial.

 

Which tool should you use?

 

A.

Performance Monitor

B.

Database Engine Tuning Advisor

C.

Data Profile Viewer

D.

Extended Events

 

Answer: D

Explanation:http://msdn.microsoft.com/en-us/library/bb677278.aspx http://msdn.microsoft.com/en-us/library/bb630282.aspx http://www.sql-server-performance.com/2006/query-execution-plan-analysis/ http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in- sqlserver/

http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last%2c+execution+plans+ show+true+thread+reservations./92458/

 

 

 

http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query- executionplan.aspx

http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goals-gone-rogue.aspx http://msdn.microsoft.com/en-us/library/bb895310.aspx http://msdn.microsoft.com/en-us/library/bb895313.aspx http://msdn.microsoft.com/en-us/library/hh231122.aspx

 

 

QUESTION 200  (Topic 13)

 

You plan to modify a stored procedure to use temporary data.

 

The stored procedure must meet the following requirements:

 

Favor physical memory when physical memory is available. Be able to roll back changes to the temporary data.

 

You need to recommend which object to add to the stored procedure.

 

Which T-SQL command should you recommend?

 

A.

CREATE TABLE ##Table…

B.

CREATE TABLE Table…

C.

CREATE VIEW Table…

D.

CREATE PARTITION SCHEME Table…

E.

DECLARE TABLE @ Table…

 

Answer: A

Explanation: Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sig
n (##table_name)

 

 

QUESTION 201  (Topic 13)

 

 

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.

 

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 202  (Topic 13)

 

You execute the following code:

 

 

 

clip_image010

 

You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1.

 

Which code segment should you use?

 

clip_image012

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Answer: B

 

 

QUESTION 203

DRAG DROP  (Topic 13)

 

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code:

 

clip_image014

 

Two million rows are added to dbo.Sales.

 

You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.

 

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.

 

 

 

clip_image016

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 204  (Topic 13)

 

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: D

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

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

 

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

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