Archive | SQLServer RSS feed for this section

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?

Comments { 0 } Posted on August 16, 2011 in SQL, SQL Server 2000, SQL Server 2005, SQLServer, SQLServerPedia Syndication

Fill up my Kindle

I finally broke down and got a Kindle 3G Wireless 6″ from Amazon. I’ve been reading a few titles from the Kindle for PC software trying to determine how tech books would show up to see if I would lose anything by not getting the real book but it’s hard to tell. I asked on Twitter a while back and only got one response saying that there weren’t many tech books on the Kindle.

Well, here’s your chance to post your first comment on the ol’ blog. Are you an author who’s book on Kindle show’s up beautifully? Are you an MVP who carries your Kindle around with you? Hoping SQL Server MVP Glenn Berry (Blog|Twitter) gets his way and more great books come to the Kindle. I’ll help him by clicking the link on the Amazon page for his book telling the publisher we want the book on the Kindle. I’ll probably buy the hard copy anyway because of the rave review Brent Ozar (Blog|Twitter) gave it, and the fact that it’s the only book of its kind that I’ve seen. Maybe Glenn can right all the wrongs in the SQL Server building world. I’m pulling for him.

So, if you have a tech book (SQL or not) that you can say for sure looks great on the Kindle and doesn’t lose content or readability by being on the Kindle then please post it here in the comments.

As always, if you have comments or suggestions on how I can make this blog a better resource then please feel free to share. And for the few brave souls who’ve made it to the last sentence of this post…I’ll let you know there’s a big redesign of the blog coming up which will hopefully improve the focus and readability.

Update: Not sure why I didn’t get the email notifying me that Glenn had responded to my comment on his blog, but the book is now available on the Kindle.

Comments { 2 } Posted on August 5, 2011 in SQLServer, SQLServerPedia Syndication