SQL Server 2000 Delete Top (1000)

sql sql-server optimization sql-delete
Kevin picture Kevin · Dec 18, 2009 · Viewed 26.7k times · Source

I have a large SQL Server database with a table at about 45 million records. I am archiving this table, and need to remove all entries greater than two years ago. I have the inserting into my archive table working fine, but I'm having issues with efficiency when deleting.

My problem lies within the indexes currently on the table. I would like to delete (and archival insert) in 1000 record chunks. To do this, I need to determine the "top" 1000 records fulfilling the requirement (greater than two years old). The DateTime stamp on the row is a clustered index, so this is great for grabbing the rows. However SQL 2000 does not allow DELETE TOP 1000.... so I need to do something like:

DELETE FROM <table> WHERE [UniqueID] IN 
(SELECT TOP 1000 [UniqueID] FROM <table> WHERE [DateTime] < @TwoYearsAgo)

This would work great, if UniqueID was indexed. Since it is not, this takes a very long time (it is scanning the table for each of the 1000 records to be deleted). There are no other indexes on the table that uniquely identify the records. I am told it would be too costly to compute an index on UniqueID, as this is a live DB. Can anyone point out a way to optimize this query?

Answer

Lasse V. Karlsen picture Lasse V. Karlsen · Dec 18, 2009

How about rewriting the query?

SET ROWCOUNT 1000
DELETE FROM <table> WHERE [DateTime] < @TwoYearsAgo

See documentation on SET ROWCOUNT (Transact-SQL).

Also note that per the documentation for DELETE, it supports the TOP clause, but that is apparently new for SQL Server 2005 and up. I'm saying this since it sounds like it isn't supported on your database server, but have you actually tried using it? I don't have access to SQL Server 2000 documentation so I'm unsure if it is supported on that version. It very well might not be.

DELETE TOP (1000) FROM <table> WHERE [DateTime] < @TwoYearsAgo

Note the difference from the way TOP on select can be written, without the parenthesis. For UPDATE, DELETE and INSERT, the expression must be parenthesized, even if it's only a constant number like above.

Related questions

Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the …

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More
Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is …

Read More