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?