The impetus for writing the blog is a recent experience we had due to a small oversight in one of the delete code implemented by a fellow team member. By the time we realized the mistake, considerable amount of the damage was already done and we had to do a good amount of work to put everything back in place.
Now onto the details.
There are few things that need to taken care of when you’re developing a DELETE procedure to clean up the data in your project. The main checks that you need to do can be summed up as below
1. Make sure the logic is correct
This is the basic thing that you need to take care of 🙂 . Do a part to part comparison of the DELETE query logic against the actual requirement and make sure the query covers all the conditions as stated by the requirement
2. Test with a sample data
Make sure you have a sandbox with sample data available in which you can test the delete logic and make sure it works exactly as per your requirement.
In cases were you don’t have such a setup available i.e. just have a single environment to work with (which is extremely dangerous IMO!) you can follow the below approach
Wrap the script within a dummy transaction as below
BEGIN TRAN TestDelete
SELECT * FROM Table
–COMMIT TRAN TestDelete
–ROLLBACK TRAN TestDelete
Check the output from the table after the delete operation and if it matches your expectation uncomment and execute the COMMIT TRAN statement. Otherwise uncomment and execute the ROLLBACK TRAN statement.
This can be applied in the cases where you want to apply manual delete script.
In case of automated jobs you can do a different approach as the above is not possible.
You can make use of OUTPUT clause to capture the contents of the DELETED table to another staging table and check it after the DELETE. If by any chance it doesn’t match you expectation you can ROLLBACK the transaction otherwise you may COMMIT it.
The stub will look like below
BEGIN TRAN TestDelete
DELETE t
OUTPUT DELETED.* INTO OtherTable
FROM Table t
…
IF (SELECT COUNT(*) FROM OtherTable) =
ROLLBACK TRAN TestDelete
ELSE
COMMIT TRAN TestDelete
3. Always fully qualify the object names used in DELETE statement
This is the most critical of the steps and the one which gave us the maximum trouble with. Make sure you use appropriate aliases and fully qualify the columns otherwise you may end up flushing out the entire data of the table as per below. This is what happened to us and we had to spend good amount of time to rework and put the lost data back in place.
See the below illustration to understand the issue
declare @t table
(
id int,
val varchar(50)
)
insert @t
values (1,’Test’),
(2,’jkgj’),
(1,’grwreg’),
(1,’sdvad’),
(1,’svdb’)
declare @t1 table
(
Col1 int,
col2 int
)
insert @t1
values (12,123),(2,234),(5,213)
select * from @t
select * from @t1
delete
from @t
where id in (select id from @t1)
select * from @t
select * from @t1
If you see the code above carefully it has a wrong column placed within the subquery. Little did we realize what this would cause to us.
Checking the output you can see that it will delete all the records from the outer table @t as it will act as a trivial condition check.
This behavior is by design and is explained in the KB article below
http://support.microsoft.com/kb/298674/en-us
Rewriting the query as below you can see that it will complain on the wrong column
delete
from @t t
where t.id in (select t1.id from @t1 t1)
Which will avoid any possibility of table getting emptied as it happened above
So the bottom line is always fully qualify your objects
Hopefully this would give you some insight into things to be taken care of while developing and implementing delete queries in future and avoid any unintended deletions from happening.
