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?

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 use SQL Server 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:
Either BOTH statements succeed or BOTH statements fail as a batch.
If an error occurs on the first statement, SQL should not attempt to run the second statement.
Error information should be returned to the client.
What should you do?

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. (Answerchoices may be used once, more than once, or not at all.)
Select and Place:

You have two existing tables, one named COUNTRY and the other named STATES.
The tables are defined as follows:

You need to set up a rule that every STATES.Country_Abbr must match an existing record in the COUNTRY
table.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Select and Place:

You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in
the following table.

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
“Msg 916, Level 14, State 1, Line 1
The server principal “Account1” is not able to access the database “DB1” under the current security context.”You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

You have a SQL Server 2012 database that contains a table named Users. The Users table contains
usernames and passwords.
You need to ensure that all new records have a password.
Which code segment should you use?
More than one answer choice may achieve the goal. Select the BEST answer.

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName.
What should you do?

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance
named Instance1. Instance1 contains a database named ContentDatabase.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue
to back up the transaction log.
Which three code segments 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.
Select and Place:

You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?