Q1. You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12 

Subquery returned more than 1 value. This is not permitted when the subquery follows =,! 

=, <, <= ,>, > = or when the subquery is used as an expression." 

You need to ensure that IndexManagement.sql executes properly. 

Which WHILE statement should you use at line 18? 

A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable) 

B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable) 

C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable) 

D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie) 

View Answer

Q2. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. 

What should you use? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. A Data Definition Language (DDL) trigger 

B. A DEFAULT constraint 

C. A CHECK constraint 

D. A FOREIGN KEY constraint 

E. A data manipulation language (DML) trigger 

View Answer

Q3. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. 

What should you use? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. A data manipulation language (DML) trigger 

B. A DEFAULT constraint 

C. A Data Definition Language (DDL) trigger 

D. A CHECK constraint 

E. A FOREIGN KEY constraint 

View Answer

Q4. You have a Microsoft SQL Azure database named DBAzure1. 

You create a table in DBAzure1 by using the following script: 

You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated. 

What should you recommend creating? 

A. A CHECK constraint 

B. A filtered index 

C. A columnstore index 

D. A UNIQUE constraint 

View Answer

Q5. While testing the CategoryFromType function, you discover that the function is returning 'Other'. 

You need to update CategoryFromType to return the category name. 

Which line of code should you modify in CategoryFromType.sql? 

A. 04 

B. 05 

C. 12 

D. 14 

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 need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails. 

Which change should you recommend? 

A. Set the XACT_ABORT option to off. 

B. Set the XACT_ABORT option to on. 

C. Set the IMPLICIT_TRANSACTIONS option to off. 

D. Set the IMPLICIT_TRANSACTIONS option to on. 

View Answer

Q8. You have a SQL Server 2012 instance. 

You plan to create an application that uses spatial data. 

You need to create an object that will support the representation of the surface area of all the oceans. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

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

View Answer

Q10. You need to recommend a solution to meet the security requirements of the junior database administrators. 

What should you include in the recommendation? 

A. A server role 

B. A database role 

C. A credential 

D. A shared login 

View Answer