SQL Server – Dangers of Delete

SQL Server – Dangers of Delete

To minimise the risks that go with deleting data, I’m in the habit of writing out and testing the WHERE  clause in a separate query first. Couple that with an explicit BEGIN TRANSACTION / ROLLBACK / COMMIT  and your data should be pretty safe from unintentional deletions.

A piece of buggy T-SQL code that was shared with me from a production Stored Procedure illustrated an oddity with the way SQL Server can parse a Delete statement when using a Join. The Join can essentially be ignored if the Table being Deleted from has no Join predicate against the list of objects specified in the JOIN  clause. What results is a deletion of the entire table 😯 ! A worked example follows.

 

Test Data

tsql_DeleteMissingJoinPredicate_OddNumbersResults1

The Delete Statement

We need to remove the even numbers 4 and 6 from the OddNumbers table. The buggy T-SQL code example first:

 

The dbo.Numbers table isn’t actually of any use here – that’s the deliberate mistake. This table has no Join predicate specified but SQL Server parses the statement successfully without giving any errors. In doing so, the database engine appears to ignore everything from line 2 onwards, thereby deleting all records from dbo.Numbers.

Lets look at part of the Execution Plan:

 

tsql_DeleteMissingJoinPredicate

 

tsql_DeleteMissingJoinPredicate_ExPlanWarning2

 

The engine acknowledges that the join predicate is missing and presents a warning in the execution plan for the second Nested Loop join. This warning is transparent to the user though under normal query execution.

 

The correct Delete statement would have been:


References

Leave a Reply

Your email address will not be published. Required fields are marked *