MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

Objective 4.2: Design the Locking Granularity Level

• Practice 1 Create a simple table with one column and no primary key. Insert two rows into the table. Start a transaction (without committing it), and update one of the rows in the table. In another connection, try to read the unaffected row, both rows, and the affected row, and watch the result. Do this for all different isolation levels (including read committed snapshot), and observe the differences in behavior.

Objective 4.4: Design Code that Uses Transactions

• Practice 1 Create a simple table with one integer column, and declare it as the table's primary key. Within a user-defined transaction, insert the values 1, 1, and 2 into the table. Check whether any rows were persisted into the table (or if they were rolled back). Experiment with using the SET XACT_ABORT ON setting as well as using a try/catch block around the transaction. Also, always issue a SELECT XACT_STATE(); query at the end of each batch to see the state of the transaction.