Keyvan Nayyeri

As you may know, one of the main reasons for deadlocks in a database is long execution of commands especially DELETE commands in a single transaction because a DELETE command runs a single transaction and if it takes a longer time to run then it may end with a long lock in database.

SQL Server 2005 came with the introduction of new T-SQL enhancements including some improvements for TOP clause. Previously you could use TOP clause only in SELECT statements with literal values. In new version of T-SQL you can use it with dynamic values and also apply it to DELETE and UPDATE statements.

One of the techniques that I apply to my projects to reduce the lock on database for DELETE and UPDATE statements is using TOP clause to run my commands in several batches.

This is easy to implement and use and can have a huge impact on database performance when you're dealing with such DELETE and UPDATE commands.

The technique is very simple: you use a TOP clause for your commands in a loop to run your DELETE or UPDATE commands and run this loop until the command executes for all specified rows in the database.

An example would clarify this. Suppose that I have a table named SampleTable with a very simple structure that has an ID and a Value column. Following stored procedure would insert 1000 rows into this table.

Now to delete these rows in SampleTable you can simply write a single DELETE command but for large number of rows this can yield lots of locks and reduce the overall performance. But here I write another stored procedure that runs such a command with TOP clause in a loop until all rows are deleted. Since this is running for small number of rows this can have a good effect on performance.

This latter stored procedure has a very simple logic. It gets a batch size as its parameter and uses it in the DELETE command. It executes this command in a loop until Rows is larger than zero. Since Rows is equal to the number of affected rows in the last execution it will be larger than zero until all rows are deleted!

You can apply same technique to UPDATE commands as well.