DELETE using SYNONYMS

It’s all about how we used a synonym for a table in a DELETE statement.

Since SYNONYMS are not commonly used, I’ll take a moment to describe them. Synonyms, besides being tough to say, are used in SQL Server to create an alternate name for a database object.  In my case, I like to use synonyms to replace long table names.  I don’t use synonyms in production code, but if I’m testing and doing corrective updates they are fair game.

I’m not going to use the actual tables to demonstrate this anomaly, lest the legal team pay me an unwanted visit.
In this block of code I’ll create a fictitious table, create a synonym for it (“lt” = LONG TABLE), load data into the table using the synonym, and select from the synonym to show the data actually loaded:

USE tempdb;
GO
CREATE TABLE dbo.this_is_one_long_table_name (    id    int,

whocaresaboutdata varchar(50));
CREATE SYNONYM lt FOR dbo.this_is_one_long_table_name;

INSERT INTO lt VALUES    ( 1, 'I do'),
                        ( 2, 'Do You?');

SELECT * FROM lt;

The result of that query is:


So far nothing too exciting.
Anyone who has ever had to do anything other than the most simple DELETE knows that DELETE can have two FROM clauses.  It’s awkward, but that’s how it is.
First let’s finish loading our little table…

INSERT INTO dbo.this_is_one_long_table_name (id)
    VALUES    ( 3 ), (4), (5), (6), (7), (8), (9), (0);

SELECT * FROM lt ORDER BY id;

OK, so now we have 0 – 9 loaded into our test table.  We don’t need much data to run this test.
Now let’s delete some data out of our table.  To do that we’ll create another table, delete_these_records, that will have the IDs that we want to delete:

CREATE TABLE dbo.delete_these_records ( id    int );
INSERT INTO dbo.delete_these_records VALUES (2), (4), (8);

-- DELETE the data using a JOIN and SYNONYM
BEGIN TRAN
DELETE FROM dbo.this_is_one_long_table_name
FROM    lt lt
        INNER JOIN dbo.delete_these_records dr
        ON dr.id = lt.id;

(10 row(s) affected)    

ROLLBACK;

If you’re following this, you’ll expect the above query to delete the rows where the ID IN (2, 4, 8).  Instead, it deleted all of the records in this_is_one_long_table_name.  Another happy time for ROLLBACK.
It turns out, after some experimentation and review of the execution plans, the issue is in the differences in the first and second FROM clauses.  If both clauses consistently use either the full table name in both places OR uses the synonym in both places, the query will work as expected.
For example, both of these queries will work as expected, ie., deletes IDs 2, 4 and 8.

BEGIN TRAN
DELETE FROM dbo.this_is_one_long_table_name
FROM    dbo.this_is_one_long_table_name lt
        INNER JOIN dbo.delete_these_records dr
        ON dr.id = lt.id;
ROLLBACK

BEGIN TRAN
DELETE FROM lt
FROM    lt lt
        INNER JOIN dbo.delete_these_records dr
        ON dr.id = lt.id;
ROLLBACK

At this point in time I surmised that if a table has a synonym associated with it, and a DELETE statement uses BOTH the actual table name and the synonym in the same query, the second FROM clause will basically get ignored.
It turns out, that if a careless SQL developer created the second FROM clause that did not link back to the table being deleted, ALL OF THE ROWS WILL BE DELETED.  The second FROM clause and any JOINs in it will be logically ignored.  No warnings.  No errors.  All data is gone!
The following example will delete ALL 10 records.  The second FROM clause does not include a reference to the table in the first FROM clause.  The query passes the parser, algebrizer and optimizer…  and proceeds to delete the entire table.

BEGIN TRAN
DELETE FROM dbo.this_is_one_long_table_name
FROM    dbo.delete_these_records d1
        INNER JOIN dbo.delete_these_records d2
        ON d1.id = d2.id;
ROLLBACK

I first observed this query behavior in SQL 2005 Enterprise Edition.  The query for this post is in SQL Server 2008 Developer Edition.  The version of SQL Server does not appear to be a factor.
CONCLUSION:  The second FROM clause in a DELETE statement must exactly reference the table name in the first FROM clause.  If it doesn’t, the DELETE will act as if the second FROM clause doesn’t even exist.  (Actually, that’s not quite true either, because a review of the query plan will show the filter taking place in the second FROM clause and then JOINs the entire dataset… resulting in all records being deleted.  So the query is actually doing more work than a straight forward DELETE.)
The use of SYNONYMS MUST BE CONSISTENTLY APPLIED to both FROM clauses.  If one clause uses a SYNONYM and the other the actual table name, the second FROM clause will be logically disconnected from the query and ALL RECORDS IN THE TABLE will be deleted.
Going one step further, we also recognized that if the second FROM clause does not include a table reference to the first FROM clause, that table that is actually being deleted, the DELETE will ignore the second FROM clause and proceed to delete all of the records in the target table.