Question no.10

Developers report that usp_UpdateSessionRoom periodically returns error 3960. You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows. What should you configure in Procedures.sql?

A. Replace line 46 with the following code: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

B. Replace line 46 with the following code: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

C. Move the SELECT statement at line 49 to line 57.

D. Move the SET statement at line 46 to line 53.

Correct Answer: A 

Question no.9

You need to create the object used by the parameter of usp_InsertSessions. Which statement should you use?


A. CREATE XML SCHEMA COLLECTION SessionDataTable

B. CREATE TYPE SessionDataTable AS Table

C. CREATE SCHEMA SessionDataTable

D. CREATE TABLE SessionDataTable

Correct Answer: B 

Question no.8

You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements: Have a default value of false. Minimize the amount of disk space used. Which code block should you use?

A. ALTER TABLE Attendees ADD Confirmed bit DEFAULT 0;

B. ALTER TABLE Attendees ADD Confirmed char(l) DEFAULT '1';

C. ALTER TABLE Attendees ADD Confirmed bit DEFAULT 1;

D. ALTER TABLE Attendees ADD Confirmed char(l) DEFAULT `1';

Correct Answer: A 
Explanation

Explanation/Reference: 
Explanation: http://msdn.microsoft.com/en-us/library/ms177603.aspx

Question no.7

You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required. What should you add to usp_SelectSpeakersByName?

A. A table variable

B. An OFFSET-FETCH clause

C. The ROWNUMBER keyword

D. A recursive common table expression

Correct Answer: B 
Explanation

Explanation/Reference: 
Explanation: http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql- serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/- http://www.sqlservercentral.com/blogs/ juggling_with_sql/2011/11/30/using-offset-and-fetch/

Question no.6

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName. What should you include in the recommendation?

A. The Distributed Transaction Coordinator (DTC)

B. Transactional replication

C. Change data capture

D. Change tracking

Correct Answer: A 

Question no.5

You execute usp_TestSpeakers. You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used. What should you add at line 30 of Procedures.sql?

A. OPTION (FORCESCAN)

B. OPTION (FORCESEEK)

C. OPTION (OPTIMIZE FOR UNKNOWN)

D. OPTION (OPTIMIZE FOR (@LastName= 'Anderson'))

Correct Answer: C 
Explanation

Explanation/Reference: 
Explanation: http://msdn.microsoft.com/en-us/library/ms181714.aspx

Question no.4

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)

Correct Answer: B