Q1. You need to recommend a feature to support your backup solution.
What should you include in the recommendation?
A. Transparent Data Encryption (TDE)
B. Column-level encryption
C. An NTFS file permission
D. A Secure Sockets Layer (SSL)
Answer: A
Q2. You have a SQL Azure database.
You execute the following script:
You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0
B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2)
C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL
D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL
Answer: D
Q3. 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?
A. CREATE TABLE ##Table...
B. CREATE TABLE Table...
C. CREATE VIEW Table...
D. CREATE PARTITION SCHEME Table...
E. DECLARE TABLE @ Table...
Answer: A
Q4. You need to create a script that automates the export of the XML data. The script must meet the integration requirements.
What should you include in the script?
A. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
D. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
Answer: B
Q5. You need to configure security on the Product table for customer support representatives.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID.
B. GRANT ALL on CustProduct TO OurDomain\CustomerSupport
C. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.
D. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice.
E. GRANT SELECT on CustProduct TO OurDomain\CustomerSupport.
F. GRANT SELECT on CustProduct TO public.
Answer: AE
Q6. 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. Define an XML column in Table1 by using an XML schema collection.
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. use the modify method to insert the XML schema into each row of the XML column in Table1.
Answer: ABD
Q7. You need to recommend a solution to minimize the amount of time it takes to execute USP_5.
What should you include in the recommendation?
A. Enable cross-database chaining.
B. Use a server role to group all logins.
C. Use the EXECUTE AS clause in USP_5.
D. Copy USP.5 to each database.
Answer: A
Q8. You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Define the view by using the SCHEMABINDING option.
C. Define the view by using the CHECK option.
D. Materialize the view.
Answer: C
Q9. 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?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function
Answer: C
Q10. 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. 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.
B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
D. 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.
Answer: C