Deleting millions of rows

There are more than few times I have had to delete a lot of data — not trucate just delete a lot based upon where clause, this is to create test databases, reload data etc

DELETING every 1000 rows not only makes it faster, but in case you want to cancel the query that can happen quickly too as your transaction logs aren’t going to grow

More info

1 SELECT 1
2 WHILE @@ROWCOUNT > 0
3 BEGIN
4 DELETE TOP (1000)
5 FROM LargeTable
6 END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s