Which code segment or segments should you use to complete the Transact-SQL statements?

DRAG DROP

You develop an application that uses data from a Microsoft SQL Server database.

A stored procedure named MyProcedure experiences excessive blocking problems.

You need to enable row versioning.

However, you do not want to force every connection to have row versioning enabled.

Which code segment or segments should you use to complete the Transact-SQL statements? To answer, drag the appropriate command or commands to the appropriate position or positions in the answer area to complete the Transact-SQL statements. Answer choices may be used once, more than once, or not at all. Answer targets may be used once. Additionally, you may need to drag the split bar between panes or scroll to view content.

View Answer

Answer:

Explanation:

Box 1: SET ALLOW_SNAPSHOT_ISOLATION ON

Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

The following Transact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2008R2

SET ALLOW_SNAPSHOT_ISOLATION ON;

Incorrect: Not READ_COMMITTED_SNAPSHOT

When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database.

Box 2: SET TRANSACTION ISOLATION LEVEL SNAPSHOT

The ALLOW_SNAPSHOT_ISOLATION ON option transactions can specify the SNAPSHOT transaction isolation level.

References:

https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options