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

Ensurepass

QUESTION 185

DRAG DROP  (Topic 13)

 

You are planning two stored procedures named SProc1 and SProc2.

 

You identify the following requirements:

 

SProc1 must return a table.

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_image002

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 186  (Topic 13)

 

You execute the following code.

 

clip_image004

 

After populating the Employees table with 10,000 rows, you execute the following query:

 

clip_image006

 

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

 

What should you do?

 

A.

Partition the table and use the JobTitle column for the partition scheme.

B.

Change SUBSTRING(JobTitle,l,l) = ‘C to JobTitle LIKE ‘c%’

C.

Change SUBSTRING (JobTitle, 1,1] = ‘c’ to LEFT(JobTitle ,1) = ‘c’.

D.

Replace IX_Employees with a clustered index.

 

Answer: B

 

 

QUESTION 187

DRAG DROP  (Topic 13)

 

You are planning two stored procedures named SProc1 and SProc2. You identify the

 

 

following requirements:

 

SProc1 must return a table.

SProc2 must return a status code.

 

You need to identify which options must be implemented to meet each stored procedure requirement.

 

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_image008

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 188

DRAG DROP  (Topic 13)

 

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_image010

 

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_image012

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 189

DRAG DROP  (Topic 13)

 

You create a disk-based table that contains the following script:

 

clip_image014

 

You need to prevent duplicate values in the SKU 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_image016

 

A.

 

B.

 

C.

 

D.

 

 < /b>

Answer:

 

 

QUESTION 190  (Topic 13)

 

You have an application that uses a view to access data from multiple tables.

 

 

You need to ensure that you can insert rows into the underlying tables by using the view.

 

What should you do?

 

A.

Create an INSTEAD OF trigger on the view.

B.

Define the view by using the SCHEMABINDING option.

C.

Define the view by using the CHECK option.

D.

Materialize the view.

 

Answer: C

Explanation:

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

 

 

QUESTION 191

DRAG DROP  (Topic 13)

 

You have a database that contains three tables. The tables are configured as shown in the following table.

 

clip_image018

 

You have the following query:

 

 

 

clip_image020

 

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)

 

clip_image022

 

You need to create one index to minimize the amount of time it takes to execute the query.

 

What should you do?

 

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

 

 

 

clip_image024

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 192

DRAG DROP  (Topic 13)

 

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 7000 IOPS, 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.

 

 

 

clip_image026

 

A.

 

B.

 

C.

 

D.

 

 

Answer:

 

 

QUESTION 193  (Topic 13)

 

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

 

 

QUESTION 194  (Topic 13)

 

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:

 

clip_image028

 

 

 

clip_image030

 

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

 

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…