Deleting A Small Number Of Rows From A Big Table

Kevin Feasel

2018-04-30

T-SQL

Brent Ozar has a tip for deleting a relatively small percentage of data from a very large table:

Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%.

It’s especially painful if you need to do regular archiving jobs, like deleting the oldest 30 days of data from a table with 10 years of data in it.

The trick is making a view that contains the top, say, 1,000 rows that you want to delete:

Read on for a demo.

Related Posts

Reading SQL Server Error Logs

Thomas Rushton has a script for us: Why Script This? What’s Wrong With SSMS’s GUI?Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I […]

Read More

Rounding to Intervals in Various Languages

Dave Mason doesn’t like rounding to intervals very much in T-SQL: If I had to choose any of these options for production, I’d probably go with Query 1, just because I think it would make the most sense to any other developer that might encounter it (including future me, who probably wouldn’t remember writing the […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30