Perform fast MSSQL Delete operations
The solution focuses on utilizing a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.
Before:
delete from t1 where a in (select top (10000) a from t1 order by a);
After:
create view v1 as (select top (10000) * from t1 order by a) delete from v1
You can also use this method for update operations aswell:
update t1 set b = 'y' from (select top (100) b from t1 order by a ) t1
The posts that provided this information referenced a link (http://blogs.msdn.com/sqlcat/archive/2009/05/21/fa...) provided by Kevin Stephenson of MySpace and Lubor Kollar, a member of the SQL Server Customer Advisory Team. This link is now unavailable but the Internet Archive has a copy of this page at http://web.archive.org/web/20100212155407/http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx.