DRAG DROP
You use the following statement to create a table.

You have the following queries.

You need to create an index to minimize the execution time of the queries.
How should you complete the statement? To answer, drag the appropriate code elements to the correct
locations. Each code element 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.
Select and Place:

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 must 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.)
Select and Place:

You have the following stored procedure.

The stored procedure takes much longer to execute than expected.
While reviewing the execution plan of the stored procedure, you discover the following predicate for a Clustered
Index Scan operator.

You need to resolve the performance issue.
What should you do?

You are designing a new database table that will be used for reporting.
You define the table by using the following statement.

You need to store the data in the table by using the least amount of storage space possible.
Which storage option should you use?

DRAG DROP
You need to identify which nonclustered indexes are unused by queries.How should you complete the statement? To answer, drag the appropriate values to the correct locations. Each
value 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.
Select and Place:

DRAG DROP
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.You need to perform the following maintenance tasks:
Identify unused indexes.
Identify which indexes should be created.
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices
may be used once, more than once, or not at all.)
Select and Place:

You have a database named Inventory. Inventory is stored on Microsoft Azure SQL Database and contains a
table named ProductDetails.
For a given product, you need to retrieve the ProductID, Manufacturer, Weight, and ListPrice columns for all of
the items in the ProductDetails table. The solution must ensure that the results can be joined to other tables.
Which Transact-SQL statement should you run?

You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM
available. There is approximately 50GB of storage space available on a slow local disk. You create a new
stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from
which you will compute two complex business scores.
The stored procedure will use temporary storage defined as follows:

The code will make several passes through the data, applying complex calculations before writing the data to a
permanent disk-based table in the same database from which it reads the data.
For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical
disk pressure.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

You have a Microsoft SQL Azure database.
You have the following stored procedure:

You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must
minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?

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.
Select and Place: