Deleting Data in Small Chunks on SQL Server

Chunky Shirt

I’ve had this post on the backburner for a bit of time, but Glenn Berry (Blog|Twitter) recently posted on gradually deleting data in SQL Server and that made me think of it again. I would advise looking at his post for guidance on how to effectively clean up your data in chunks, but I want to give you a little more background on what made me want to post this.

For some time, I’ve been using the following code to perform the task:


SET ROWCOUNT 10000
--DECLARE @Text varchar(255)

--SET @Text = 'Deleted 10000 rows'
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
WHILE @@rowcount > 0
BEGIN
DELETE  FROM [database].[dbo].[table]
WHERE   DateChecked < '2011-01-01'
--PRINT (@Text)
END
SET ROWCOUNT 0
GO

So when I answered a post on ask.sqlservercentral.com that seemed to be fitting for my answer, I got the opportunity to learn a few things that I wasn’t aware of. Pavel Pawlowski (Blog|Twitter) pointed out that it’s better not to use SET ROWCOUNT.

On SQL Server 2005+ it’s better to use DELETE TOP 10000 instead of SET ROWCOUNT 10000. Also you can read on MSDN SET ROWCOUNT (Transact-SQL).

He mentions SQL Server 2005 and later, but what about SQL 2000? Does anyone have a preferred method when dealing with SQL Server 2000 that’s different? Is there a one size fits all?

, ,