DRAG DROP
You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named
FG1, FG2, FG3, and FG4. You execute the following code:

Two million rows are added to dbo.Sales.
You need to move the data from the first partition to a new table named SalesHistory and,
starting on December 31, 2012, repartition dbo.Sales to support new sales data for three
months.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the
answer area and arrange them in the correct order.

You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is
used for auditing is updated.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.

You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter
named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that
only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)

You have an index for a table in a SQL Azure database. The database is used for Online
Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?

You have a SQL Server 2012 database named Database1.
You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

You have database objects that were created by using the following script:

The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.

The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit
button.)

You need to ensure that usp_GetCustomersByDate completes as quickly as possible.
What should you do?

DRAG DROP
You plan to deploy two stored procedures named SP1 and SP2 that read data from the
database.
Your company identifies the following requirements for each stored procedure:
SP1 must allow dirty reads.
SP2 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.)

DRAG DROP
You execute the following code:

You discover that the Customers table was created in the dbo schema.
You need to create a code segment to move the table to another schema named Schema2.
What should you create?
To answer, drag the appropriate code segments to the correct location in the answer area.
(Answer choices may be used once, more than once, or not at all.)

You have a database named DB1.
You plan to configure change data capture on the existing tables in DB1.
The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)

You need to minimize disk contention caused by change data capture.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent
users.

You need to log all of the queries to DB1 that become deadlocked. The solution must
minimize the impact on the server.
What should you create?