Q1. You have a table named Rooms that contains three columns. You execute the following query: 

You discover the execution plan shown in the exhibit. (Click the Exhibit button.) 

You need to recommend a solution to reduce the amount of time it takes to execute the query. 

What should you do? 

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

A. Include the RoomName column and the Position column in the Room_IX index. 

B. Create a nonclustered index for RoomName, Id, and Position. 

C. Create a clustered index for Id. 

D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint. 

View Answer

Q2. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName. 

What should you include in the recommendation? 

A. Change data capture 

B. Change tracking 

C. Transactional replication 

D. The Distributed Transaction Coordinator (DTC) 

View Answer

Q3. You need to implement a solution that meets the security requirements. Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q4. You use SQL Server 2014 to maintain the data used by applications at your company. 

You want to execute two statements. 

You need to guarantee that either both statements succeed, or both statements fail together as a batch. 

Which code should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

View Answer

Q5. You need to recommend a solution for the error handling of USP_4. The solution must handle errors for nested stored procedures in the code for USP_4. 

What should you recommend? 

A. Use the @@ERROR variable in the nested stored procedures. 

B. Use the @@ERROR variable in USP_4. 

C. Use the RAISERROR command in the nested stored procedures. 

D. Use the RAISERROR command in USP_4. 

View Answer

Q6. You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data. 

Each order has a sales total that can only be discovered by querying multiple tables. 

You need to ensure that the value of the sales total is returned by executing a query on Orders. 

What should you create? 

A. A calculated column that uses a scalar function 

B. A trigger that uses a table-valued function 

C. A calculated column that uses a table-valued function 

D. A trigger that uses a ranking function 

View Answer

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

View Answer

Q8. You need to create a function that filters invoices by CustomerID. The SELECT statement 

for the function is contained in InvoicesByCustomer.sql. Which code segment should you use to complete the function? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q9. An administrator provides a digital certificate named ServerCert. 

You need to implement Transparent Data Encryption (TDE) on ProductsDB. 

Which code segment should you use? 

A. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO B. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

C. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

D. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

View Answer

Q10. You need to encapsulate a T-SQL script into a reusable user-defined object. 

The object must meet the following requirements: 

. Permit insertions into a table variable. 

. Support structured exception handling. 

. Prevent changes to the definition of referenced objects. 

. Support the use of the APPLY operator on the output of the object. 

Which type of object should you use? 

A. An inline table-valued function 

B. A stored procedure 

C. A scalar user-defined function 

D. A multi-statement table-valued function 

View Answer