DRAG DROP
You have a SQL Server 2012 database named Database1. Database1 has a data file
named database1_data.mdf and a transaction log file named database1_Log.ldf.
Database1_Data.mdf is 1.5 GB.
Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can
performtransaction log backups in the future. Which code segment should you execute? To
answer,move the appropriate code segments from the list of code segments to the answer
area andarrange them in the correct order.

DRAG DROP
You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2
thatwill have the following specifications:
Table1 will contain a date column named Column1 that will contain a nullvalue
approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of twoother columns in
Table2.
Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution
mustminimize the storage requirements for the tables. Which options should you
recommend? Toanswer, drag the appropriate options to the correct column in the answer
area.

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

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.

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.