DRAG DROP
You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a
database.
Your company identifies the following requirements for each stored procedure:
USP_1 cannot allow dirty reads.
USP_2 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 once, or not at all.)

You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second
statement.
3. Error information should be returned to the client.
What should you do?

You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the
following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?

You have the following query on a disk-based table:

You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

You need to reduce the amount of time it takes to complete the query. You must achieve this
goal as quickly as possible.
What should you do?

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

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.

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

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:

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.

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.

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:
Prevent null values from being used.
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.)

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?