You are creating a stored procedure named usp1. Usp1 will create a table that will be used
during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize
the need to recompile the stored procedure.
Which code segment should you use to create the table?

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?

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
withthe letters JOH:
You discover that the query performs a table scan.
You need to ensure that the query uses EmployeeName.
What should you do?

DRAG DROP
Your network contains a server named Server1 that runs SQL Server 2012. Server1
contains aninstance 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 7 MB. The solution must ensure
thatyou 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
answerarea and arrange them in the correct order.

You have a SQL Server 2012 database that contains a table named Users. The Users
tablecontains 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 SQL Server 2012 environment that contains two servers. The servers are
configuredas 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
currentsecurity 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 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?

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.)

DRAG DROP
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 STATE.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.

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?