How to delete 15 million records in a table ?

Question

Azure SQL - How to delete 15 million records in a table ?

Answer 1

Simple question with a VERY complex answer which depend on many parameters that you forgot to provide.

Here are some points in short and there is more to discuss:

If you have 15,000,001 rows and you want to delete 15,000,000 then you should probably use TRUNCATE TABLE and INSERT the single row which you did not want to remove...

If you have 15,000,000 rows out of 15,000,000,000 then this means you need to remove 0.1 % and using TRUNCATE will probably not fit your case. In this case, if you have partitions that fits the rows which you want to remove, then this can make the different in the advice for best solution. For example you can create a new table with the same structure and SWITCH the partition(s) that includes the rows that you want to remove to the new table -> next you can drop the new table...

Do you have Clustered index? Probably you do! Are all the rows that you want to remove come one after the other in the physical order? This can impact the solution I would choose probably. If the rows are spread over all over the disk then I would probably choose to drop the clustered index first and re-create it after the delete but if all rows are one after the after the other then maybe using partitions is the way or

If the transaction log is almost full then you might need to delete the rows in batches (breaking delete operations up into chunks in a loop). This might fit for many cases when you delete multiple rows...

What is your bottleneck (IO, CPU, Size of disk)? Do you use compression? What tables have relations with this table? What is the Recovery model which your database use? DO you have foreign keys or triggers on the table? All these can impact the solution which I would choose probably...

I hope this gives you some options and points to think about.

If you want to dive into the issue more then please try to provide more relevant information