DELETE TOP x rows avoiding a table scan
The simplest way to delete the top X rows is use to use a
derived table.
delete
t1
from
(select top (10000) *
from t1 order by a) t1
This is one of the tricks I teach in my Advanced TSQL for Performance and
Scalabilty course
The SQLCAT team posted a solution to deleteing the TOP x rows from a table
using a view http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx.
The view isn't needed you can just use a derived table as above.
You can do similar things with update,
update
t1
set b
= 'y'
from
(select
top (100) b
from t1
order by a )
t1
If you want to know what was updated or deleted then you can use the OUTPUT
clause.
delete
t1
output
deleted.a
from
(select top (100) *
from t1 order by a) t1
update
t1
set b
= 'y'
OUTPUT
inserted.a
from
(select
top (100) *
from t1
order by a ) t1
-