순서를 지킨 top delete

if object_id('tblx') is not null
drop table tblx
go

create table tblx
(
c1 int identity(1,1)
,c2 int
)
go

insert tblx values (1)
go 30

create clustered index cl_tblx_desc on tblx (c1 desc )
go

create nonclustered index nc_tblx_asc on tblx (c1 asc )
go

delete top (10) t from tblx t with (index(cl_tblx_desc));
go

select top 20 * from tblx order by c1 desc
go

delete top (10) t from tblx t with (index(nc_tblx_asc));
go

select top 20 * from tblx order by c1 asc
go

 

 

 

 

잘라서 지우기

drop table tblx
go

select top 100 a.* into tblx
from sys.sysobjects a
, sys.sysobjects b
, sys.sysobjects c
, sys.sysobjects d
go

while (1=1) begin
 delete a
   from (select top 1000 *
     from tblx 
    where xtype = 's') a
 if @@rowcount = 0 break
 waitfor delay '00:00:00.100'
end
go

select count(*) from tblx
go

 

아래와 같이도 된다.

delete top (1000)
from tblx
where xtype = 's'