Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Handling Long-Running Transactions

Long-running transactions are transactions that create and hold locks for a long time. They include large-scale updates, deletes, and massive inserts. Monthly maintenance programs that move data into an archive directory are a typical example. For instance, Listing 13.7 would move the Orders and Order Details for a month from active to (make-believe) archive tables.

Code Listing 13.7. An Example of a Long-Running Transaction

1: set xact_abort on 2: begin transaction 3: declare @OrderList table (OrderID int) 4: insert @OrderList (OrderID) 5: select OrderID 6: from Orders with (readpast) 7: where OrderDate >= '8/1/2000'and 8: OrderDate < '9/1/2000' 9: insert into Orders_Archive ( 10: OrderID, CustomerID, EmployeeID, ...