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

• A. Use the modify method to insert the XML schema into each row of the XML column in Table1.
• B. Create an XML schema collection in the database from the text file.
• C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
• D. Define an XML column in Table1 by using an XML schema collection.

You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet
the following requirements:
The strategy must not disrupt backup operations.
DB1 must be online while the changes are deployed.
You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
---

• A. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
• B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
• C. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
• D. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

DRAG DROP
You plan to deploy SQL Server 2012.
You must create two tables named Table 1 and Table 2 that will have the following
specifications:
- Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
- Table2 will contain a column named Column2 that is the product of two other 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 must minimize the storage requirements for the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.

You need to recommend a solution that addresses the index fragmentation and index width issue.
What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)
What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Change the data type of the lastModified column to smalldatetime.
• B. Remove the modifiedBy column from the clustered index.
• C. Change the data type of the modifiedBy column to tinyint.
• D. Remove the lastModified column from the clustered index.
• E. Change the data type of the id column to bigint.
• F. Remove the id column from the clustered index.

Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data
into Table2.
A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2.
You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.
What should you do?

• A. Grant User1 the INSERT permission on Table2.
• B. Change sp1 to run as the sa user.
• C. Add User1 to the db_datawriter role.
• D. Grant User1 the EXECUTE permission on sp1.