How does SQL Server know which rows to delete?
To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end.
First, let's change the query a little bit to something that should throw an error, but doesn't.
FROM table1 AS A
FROM table2 B
WHERE B.id = A.id
If you were to just run
SELECT 1/0 you'd get a divide by zero error. But there are places where expressions are present in the query for parsing, but aren't actually projected by the optimizer.
The "list of ones" you're referring to doesn't ever really appear. All the work to identify rows to delete is done via the where clause in the exists.
Reading the plan from right to left is the correct method when you need to understand the flow of data. Left to right is the flow of logic.
- A full scan of both tables
- A hash join to match rows
- A delete from
The hash join figures out which rows need to go, and passes qualifying bookmark values on to the delete operator. In this case, bookmarks are used to uniquely identify rows because there's no clustered index available to use keys from.
With a unique clustered index, key values alone can be used to identify rows. With a non-unique clustered index, an additional unique-ifier will accompany any duplicate values, though this won't be visible to you.
DROP TABLE IF EXISTS table1, table2;
CREATE TABLE dbo.table1 (id int NOT NULL);
CREATE TABLE dbo.table2 (id int NOT NULL);
dbo.table1 WITH(TABLOCK) (id)
ROW_NUMBER() OVER(ORDER BY 1/0) AS n
FROM sys.messages AS m
) AS x;
dbo.table2 WITH(TABLOCK) (id)
FROM dbo.table1 AS t
WHERE id % 2 = 0;
SELECT TOP (100) t1.* FROM dbo.table1 AS t1;
SELECT TOP (100) t2.* FROM dbo.table2 AS t2;