Q1. You need to prepare the database to use the .NET Framework ProcessProducts component. 

Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

G. Option G 

View Answer

Q2. You are testing disaster recovery procedures. 

You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1 Cannot find server certificate with thumbprint ,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\ Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore DB1 to a different server. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q3. You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure? 

A. SAVE TRANSACTION WITH MARK @transactionID 

B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID 

C. BEGIN TRANSACTION WITH MARK @transactionID 

D. COMMIT TRANSACTION @transactionID 

View Answer

Q4. You execute the following code: 

You have a stored procedure that includes the following SELECT statement: 

You need to create a covering index on UserInfo. Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q5. You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU). 

You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits. 

The solution must minimize the amount of development effort required. 

What should you include in the recommendation? 

A. A FOREIGN KEY constraint 

B. A trigger 

C. A user-defined data type 

D. A CHECK constraint 

View Answer

Q6. You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2. 

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table. 

Schema1.Udf1 was defined by using the following code: You need to write a query that will contain the following columns: 

... 

Country CountryID CustomerName 

The solution must meet the following requirements: 

.. 

Rows must be returned only if the function returns data. 

The amount of time it takes the query to execute must be minimized. 

Which query should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q7. You have a SQL Azure database. You execute the following code: 

The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows. 

You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements. 

Which code segment should you execute? 

A. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NOT NULL 

B. CREATE INDEX IX_Customers ON Customers (FaxNumber) WITH FILLFACTOR=0 

C. CREATE INDEX IX_Customers ON Customers (CustomerName) INCLUDE (FaxNumber) 

D. CREATE INDEX IX_Customers ON Customers (FaxNumber) 

E. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NULL 

View Answer

Q8. You have a database that uses the following management views: 

.... 

Sys.dm_os_volume_stats Sys.dm_db_partition_stats Sys.dm_db_file_space_usage Sys.fulltext_indexes 

You plan to migrate the database to Microsoft SQL Azure. You need to identify which view can be used in SQL Azure. Which view should you identify? 

A. sys.fulltext_indexes 

B. sys.dm_db_file_space_usage 

C. sys.dm_os_volume_stats 

D. sys.dm_db_partition_stats 

View Answer

Q9. Topic 8) 

You execute the following code: 

You discover that the Customers table was created in the dbo schema. 

You need to create a code segment to move the table to another schema named 

Schema2. 

What should you create? 

To answer, drag the appropriate code segments to the correct location in the answer area. 

 (Answer choices may be used once, more than once, or not at all.) 

View Answer

Q10. You plan to execute the following code: 

You need to identify how many rows will be in dbo.Table1 after you execute the code. 

How many rows should you identify? 

A. 0 

B. 1 

C. 2 

D. 3 

View Answer