CTE to delete large amounts of data


I have an archiving exercise to undertake and one of my concerns is the resources it will take to do the deletes from the live table. I have been investigating using cte and dumping a list of id's here and then deleting based on this list. Not having used cte's before, will this be a more efficient way of working with this type of data? This delete will run every day and is in the region of 1 million rows per day. Does this mean that there is only one logged event (delete after cte definition) as opposed to 1 million (delete using where clause)?

Kind Regards M

more ▼

asked Jul 22, 2010 at 08:03 AM in Default

avatar image

59 7 7 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A cte is just a definition over physical tables, and isn't invoked until referenced - so any resource use in deleting data straight from the base tables will be the same whether or not the cte is used.

Also using the cte won't turn your query into a bulk-logged query - the individual row deletes will still be logged.

You may have to experiment and see how long it takes to delete 1 million rows, then break up the query to chunk it depending on your results, using say DELETE TOP n, to give other transactions a chance to access the table between DELETEs, and keep the transaction log size down (if in FULL recovery). The idea you have is good, but I'd use a temporary table to build a list of IDs for deletion, then repeat the 'chunk' until the list is processed.

If possible it may be worth scheduling the delete for a time when the database is least used.

more ▼

answered Jul 22, 2010 at 08:32 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 22, 2010 at 08:03 AM

Seen: 2856 times

Last Updated: Jul 22, 2010 at 08:03 AM

Copyright 2018 Redgate Software. Privacy Policy