New Updated Microsoft MCSE 70-464 Real Exam Questions and Answers Download 141-150

Ensurepass

QUESTION 141

DRAG DROP

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables.

 

The tables are configured as shown in the following table.

 

clip_image001

 

You plan to create indexes for the tables.

 

You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.

 

Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.

 

clip_image002

 

Correct Answer:

clip_image003

 

 

QUESTION 142

DRAG DROP

You create a table that contains the following script:

 

clip_image004

 

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.

 

clip_image005

 

Correct Answer:

clip_image006

 

 

QUESTION 143

You plan to create a new table that has the following requirements:

 

clip_image008Uses a GUID data type as the primary key.

clip_image008[1]Uses a clustered index as the primary key.

clip_image008[2]Minimizes fragmentation.

 

You need to recommend which option to include in the CREATE statement.

 

Which option should you include?

 

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

 

A.

NEWID

B.

@@IDENTITY

C.

NEWSEQUENTIALID

D.

IDENTITY

 

Correct Answer: C

 

 

QUESTION 144

You have a SQL Server 2012 instance named SQLInstance1. 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:

 

clip_image008[3]Remain online during the defragmentation.

clip_image008[4]Update distribution statistics.

clip_image008[5]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

 

Correct Answer: B

 

 

QUESTION 145

You have a SQL Azure database. You need to identify which keyword must be used to create a view that will be indexed. Which keyword should you identify?

 

A.

SCHEMABINDING

B.

VIEW_METADATA

C.

DISTINCT

D.

DEFAULT

 

Correct Answer: A

Explanation:

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

 

 

QUESTION 146

You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails. For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables. Which code segment should you execute?

 

clip_image009

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 147

You create a view by using the following code:

 

clip_image010

 

Several months after you create the view, users report that the view has started to return unexpected results.

 

You discover that the design of Table2 was modified since you created the view.

 

You need to ensure that the view returns the correct results.

 

Which code segment should you run?

 

clip_image011

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: D

 

 

QUESTION 148

DRAG DROP

You are planning two stored procedures named SProc1 and SProc2.

 

You identify the following requirements:

 

clip_image008[6]SProc1 must return a table.

clip_image008[7]SProc2 must return a scalar value.

 

You need to identify which option must be implemented for each stored procedure to return the desired data.

 

Which options should you identify?

 

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

 

clip_image013

 

Correct Answer:

clip_image015

 

 

QUESTION 149

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. Server5 contains 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.

 

Correct Answer: ABCD

 

 

QUESTION 150

 

You need to encapsulate a T-SQL script into a reusable user-defined object.

 

The object must meet the following requirements:

 

clip_image008[8]Permit insertions into a table variable.

clip_image008[9]Support structured exception handling.

clip_image008[10]Prevent changes to the definition of referenced objects.

clip_image008[11]Support the use of the APPLY operator on the output of the object.

 

Which type of object should you use?

 

A.

An inline table-valued function

B.

A stored procedure

C.

A scalar user-defined function

D.

A multi-statement table-valued function

 

Correct Answer: C

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-464 Real Exam

Try Microsoft MCSE 70-464 Free Demo