improve query execution in sql server

There are many times that you want to delete a large batch of records, or do a bulk update of some type.

Many times when you do this you will cause locking / blocking of other users - This can be a VERY bad thing for a production database.

You can avoid this generally using this type of query:

SET ROWCOUNT 500
process_more:
DELETE FROM TableName WHERE Condition = 1
IF @@ROWCOUNT > 0 GOTO process_more
SET ROWCOUNT 0

You can use DELETE or UPDATE with this method, by doing this sql will continue looping until the rows affected = 0, processing 500 at a time (or whatever you set the rowcount to be).