SQL Delete TOP statement

Question

my table (sql sever 2000) contains large amount of rows, when i delete row, it extremely slow.

i saw some post/forum that suggest to use a WHILE loop function to delete record by batch.

my sql is:

DELETE FROM Tprs_master where Fincmonth='201109';

I had rewrite to:

DELETE FROM(SELECT TOP 100 FROM tprs_master) AS t1 WHERE t1.Fincmonth='201109'

but it doesn't work.

how can I rewrite the above sql to DELETE TOP style ? Thanks

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

The MSDN DELETE page gives examples and syntax

DELETE TOP (100) FROM Tprs_master where Fincmonth='201109';

Note: SQL Server 2005+ only

Edit: OP has SQL Server 2000

SET ROWCOUNT 100
DELETE FROM Tprs_master where Fincmonth='201109';
SET ROWCOUNT 0

edited May 23 '17 at 12:19

Community

answered Oct 18 '11 at 10:09

gbn

SET ROWCOUNT for SQL Server 2000: http://technet.microsoft.com/en-us/library/ms188774.aspx – DavidEG Oct 18 '11 at 10:17
@DavidEG: I didn't mention it has side effects because it limits intermediate results too which can be confusing. Should be OK with a simple DELETE though. And SQL Server 2000 is ancient history now :) – gbn Oct 18 '11 at 10:20
@Joe Yan: which version of SQL Server please? – gbn Oct 18 '11 at 10:22
@Joe Yan: answer was updated: I guessed SQL Server 2000. You asked questions about SQL Server 2008 earlier: are you running a mixed environment? – gbn Oct 18 '11 at 10:26

1 Answers1