I have a query where I need to cross join a small table with 5 rows to a table with roughly 31k rows. The execution plan SQL Server came up with does index scans in both tables and joins them with Nested Loops (INNER JOIN). However, when checking the execution plan, I noticed that the scan on the smaller table produced 5 x 31k = 155k rows for the following step. Does that mean SQL Server is scanning the index of the smaller table 31k times?
Here is a small reproduction of the problem I'm having:
CREATE TABLE A (id INT PRIMARY KEY);
CREATE TABLE B (id INT PRIMARY KEY);
INSERT INTO A(id)
VALUES (1), (2), (3), (4), (5);
INSERT INTO B(id)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)
SELECT * FROM A CROSS JOIN B
Here is the query live statistics of the SELECT command
My question is whether the index scan on the smaller table is performing multiple I/O operations, one for each row on the bigger table, as opposed to doing it only once and having its result being fed to the following step only once.
Best Answer
Yes, for every row that comes out of A, you need to produce all of the rows for B. Since there are five rows in A, you make five trips to B.
Cross Joins can only be implemented with Nested Loops, and that's normal behavior for it.