Make delete operation fast using CTE.

--drop table t1

create table t1 (a int primary key, b char (100))

declare @i int

set @i=1

SET NOCOUNT ON

while (@i<100000)

begin

insert into t1 values (@i,'x');

set @i=@i+1

end

 ----------------------------------------------------------

-----------------------------------------------------------

set statistics io on

set statistics time on

delete from t1 where a in (select top (10000) a from t1 order by a);

set statistics time off

set statistics io off

-----------------------------------------------------------

set statistics io on

set statistics time on

;with d  as

(

select top (10000) a from t1 order by a

)

delete from d;

set statistics time off

set statistics io off

-----------------------------------------------------------