This isn’t Security related research, but it could come in handy for anyone looking to delete a large amount of data from TSQL databases.
I recently had a task where I had to delete well over 50,000 rows based on a common column value within a TSQL database. Originally I thought that a simple conditional DELETE statement would do the trick, for example:
The issue here is that the database could be so bogged down that the DELETE query could take greater than 20 seconds to remove a single record. This means it would take 11.5 days to delete 50,000 records on a single looping query. No exaggeration.
I decided that the least expensive way to do this (in terms of time) would be to do the following:
Always back up the database, do it twice.
Create a second table (TABLE2) that has the exact same schema as the original table (TABLE1). This can be done pretty easily using the CREATE TO script built into MS SQL Management Studio.
Move all the data we want, lets say records newer than 2012, from TABLE1 into TABLE2 using a pretty standard SELECT statement. You might run into some errors here if the Identity property is set on the table. So first, lets figure out if identity is set on any tables in the database:
If Identity isn’t set, we can move all the rows that are newer than 2012 from TABLE1 to TABLE2:
Find all the dependant Foreign Key mappings that TABLE1 has and make sure you record the name(s) of them. Thanks stackoverflow:
This displays all the other tables that have Foreign Keys that rely on TABLE1:
This displays the Foreign Key names that are within the tables that have Foreign Keys which rely on TABLE1.
Drop the Foreign Key associations on various dependant tables, then drop TABLE1.
Recreate TABLE1 using the same schema as TABLE2 using the same CREATE TO script in step 1.
Re-associate the Foreign Keys to the new TABLE1.
Copy all of the data from TABLE2 to TABLE1. Then drop TABLE2.
The query speeds were significantly faster, depending on your database size this can take less than an hour to do. It is much better than waiting 11.5 days and hypothetically causing downtime for a clients critical application.
Hope this helps.