Presents your 
SQL SERVER E-NEWSLETTER for January 7, 2003

<------------------------------------------->

UPDATE LARGE TABLES IN SMALL INCREMENTS TO SAVE RESOURCES

Updating very large tables should be done in small increments. If you
have a table that has 38 million rows, for example, perhaps updating 5
million rows at a time would be sufficient. If you have a process that 
is trying to update all the rows at one time, it would need significant
resources to lock the table and log the transaction.

The reasons behind updating very large tables in small increments include:

* Preventing table(s) from being locked
* Avoiding huge I/O resources being consumed
* Conserving transaction log space
* Alleviating contention by allowing concurrency
* Avoiding potential large rollbacks

Updating the entire table in one transaction requires not only a
significant locking mechanism, but also significant I/O resources. 
These types of transactions cause the system to be less than optimized 
for accommodating concurrent demands for the entire duration of the update.

If a problem occurs when updating the entire table in one
transaction--such as the transaction log not being of sufficient size 
to accommodate the process--it will cause the entire transaction to be rolled 
back. Notonly does the transaction fail to complete successfully, but it also
consumes and wastes system resources.

----------------------------------------