Delete Joined to Derived Table Causes Index Scan and Table Spool
Is this query pattern generated by this library a known query anti-pattern for a delete statement in SQL Server?


This strikes me as a slightly curious question to ask. Why does the answer to that question matter? What would you do with that information? If the library that you're using doesn't meet your business needs, then four options come to mind:


Use a different library.
Implement a workaround, for example creating a stored procedure to do the deletes with more efficient code.
Providing feedback to the author of the library.
Opening a UserVoice feedback item or support ticket with Microsoft to get better optimizer support for this type of query.


With all of that said, I think that most SQL Server people would view that query with suspicion. I also think that it's fair to say that joining to tables that you don't need is an anti-pattern.

All of the relational databases that I've worked with have limitations around the inferences that they can make for eliminating unnecessary joins. One of the reasons that you get that ugly plan in SQL Server is that there isn't optimizer support for eliminating an inner self-join on a table's primary key. This can be observed with the following simple demo:

CREATE TABLE [dbo].[a_staging_table]
(
    [staging_ky] [BIGINT] IDENTITY(1,1) NOT NULL,
    [col_p] [BIGINT] NOT NULL,
    [filler] [VARCHAR](100) NULL,
PRIMARY KEY CLUSTERED ([staging_ky] ASC)
);

INSERT INTO [dbo].[a_staging_table] WITH (TABLOCK)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE NONCLUSTERED INDEX [IDX_dbo_a_staging_table_col_p]
    ON [dbo].[a_staging_table]([col_p] ASC)
    WITH (FILLFACTOR = 100);


The following query:

SELECT j0.[staging_ky]
FROM [dbo].[a_staging_table] AS j0
LEFT OUTER JOIN [dbo].[a_staging_table] AS j1
ON j0.[staging_ky] = j1.[staging_ky];


Results in join elimination:



But a similar query with an INNER JOIN does not:



The optimizer doesn't support every possible query transformation. In this example, as programmers we can deduce that a join isn't necessary but the optimizer cannot. This might be unsupported in the product because Microsoft viewed this scenario as not common enough to address, the check was not worth the compile time overhead, or because it would be a difficult check to implement.

For completeness, the spool is present in the query plan due to halloween protection.