Performing batched updates...

Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET ROWCOUNT setting in SQL Server. Now, SQL Server 2005 provides you with a simpler construct that the optimizer can understand and use efficiently. TOP clause has been enhanced in SQL Server 2005 to support expressions and can be used now in all DML operations. Let's look at a quick example on how to use TOP with UPDATE:

 

use tempdb;
go

 

-- Create some sample data:
select * into bulkupdate from master.sys.all_objects;

 

-- Show the top 5 rows order by object_id:
select top (5) * from bulkupdate where schema_id = 4 order by object_id;

 

select CURRENT_TIMESTAMP AS UpdateStart;

 

-- Update 5 rows at at time until we are done:

while (@@rowcount>0)
    update top(5) bulkupdate set schema_id = schema_id + 1 where schema_id = 4;

select CURRENT_TIMESTAMP AS UpdateEnd;

 

-- Show the updated top 5 rows order by object_id:
select top (5) * from bulkupdate order by object_id;

 

-- Drop the table:
drop table bulkupdate;
go

 

In this example, we create a table called "bulkupdate" with some sample data from one of the system tables and update 5 rows at a time for a specific condition. Note that the order in which the rows are updated is not guaranteed with the TOP clause and it depends on the query plan. This example demonstrates how to use TOP clause in UPDATE statements to split an operation that affects large number of rows into smaller manageable units.

 

--

Umachandar Jayachandran