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.