Home » [Free] Get all latest Microsoft 70-464 Actual Tests 171-180

[Free] Get all latest Microsoft 70-464 Actual Tests 171-180

Ensurepass

QUESTION 171

DRAG DROP

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:

 

clip_image001

 

Product is an important table that has sensitive audit requirements.

 

You need to create a trigger that supports the following requirements:

 

1. Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.

2. Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.

3. Any error that occurs during the course of the trigger’s execution must prevent the changes from happening.

 

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_image003

 

Correct Answer:

clip_image005

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 172

DRAG DROP

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:

 

clip_image007Both processes must never use more than 50 percent of the CPU at any one time.

clip_image007[1]The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.

clip_image007[2]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_image009

 

Correct Answer:

clip_image011

 

 

 

 

 

 

 

 

QUESTION 173

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_image013

 

clip_image015

 

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.

 

Correct Answer: BD

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 174

DRAG DROP

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type. You have a certificate named Cert1. You need to replace Column1 with a new encrypted column that uses two-way encryption. Which code segment should you execute before you remove Column1?

 

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_image017

 

Correct Answer:

clip_image019

QUESTION 175

The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.

 

You need to update Fax to meet the following requirements:

clip_image007[3]Prevent null values from being used.

clip_image007[4]Always use an empty string instead of a null value.

 

Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)

 

clip_image020

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

E.

Option E

 

Correct Answer: ABE

Explanation:

E: First change the NULLs to ‘ ‘.

A: Then set the default to the column to ‘ ‘.

B: Finally add the NOT NULL constraint to the column.

 

 

 

 

 

 

 

 

 

 

 

QUESTION 176

DRAG DROP

You have a SQL Server 2014 database.

 

You plan to create a stored procedure that will retrieve the following information:

 

clip_image007[5]The XML content of the query plans that is stored in memory

clip_image007[6]The number of times each query plan is used

 

You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure.

 

Which dynamic management objects should you identify?

 

To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.

 

clip_image022

 

Correct Answer:

clip_image024

 

 

QUESTION 177

DRAG DROP

You plan to create a custom aggregation function named Median. You plan to deploy Median to a SQL Server 2014 server named Server1. You need to ensure that Median can access a web service named WebApp1. The solution must minimize the number of changes made to the database. You create a Microsoft .NET Framework class that contains the definition of Median. You upload a certificate to Server1. What three tasks should you perform next?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

 

clip_image026

 

Correct Answer:

clip_image028

 

 

QUESTION 178

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data. Each order has a sales total that can only be discovered by querying multiple tables. You need to ensure that the value of the sales total is returned by executing a query on Orders. What should you create?

 

A.

A calculated column that uses a scalar function

B.

A trigger that uses a table-valued function

C.

A calculated column that uses a table-valued function

D.

A trigger that uses a ranking function

 

Correct Answer: C

Explanation:

A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions. Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters.

 

Incorrect:

Not A: A scalar function would only be able to use other columns from the same table.

 

 

QUESTION 179

DRAG DROP

You administer a SQL Server 2014 instance. You have been assigned to determine the cause of frequent long-running transactions that have been tracked to the dbo.Account table, where there are many cases of blocking and deadlocks. The dbo.Account table contains more than one million rows.

 

Users and processes frequently search for and update data by using the AccountId column, and less frequently the AccountNumber and GovernmentId columns, all of which contain only unique values. Users frequently get lists of AccountNumber values by searching on Last Name and then First Name.

 

You need to modify the structure of the dbo.Account table to alleviate the issues.

 

How should you complete the table definition to reduce contention on the table structure? To answer, drag the appropriate code snippets to the correct locations in the CREATE TABLE statement. Each code snippet may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

 

clip_image030

 

Correct Answer:

clip_image032

 

 

QUESTION 180

DRAG DROP

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

 

clip_image033

 

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_image034

 

Correct Answer:

clip_image035

 

 

Instant Access to Download Testing Software & PDF File for Microsoft 70-464 Real Exam

Instant Access to Try Microsoft 70-464 Free Demo

HOT EXAM!
[Free] Get all latest Microsoft 70-465 Actual Tests 71-80
[Free] Get all latest Microsoft 70-464 Actual Tests 161-170

Name of author

Name: admin