DRAG DROP
You have a SQL Azure database named Database1. You need to design the schema for a
table named table1.
Table1 will have less than one million rows. Table1 will contain the following information
for each row:
The solution must minimize the amount of space used to store each row. Which data
types should you recommend for each column? To answer, drag the appropriate data type to
the correct column in the answer area.

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1
contains
a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount
ofnetwork traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)

You have a table named Table1 that stores customer data.
Each customer has a credit limit that can only be discovered by querying multiple tables.
You need to ensure that the value of the credit limit is returned by executing a query on
Table1.
What should you create?

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?

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

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?

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

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