Cross join yields execution plan that reads one of the tables multiple times

Question

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.

Answer 1

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.

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.

Answer 2

Yes, a nested loop join will run the bottom (inner) input for each row in the outer input.

Using your same examples data you can change the query to be a full outer join without a join condition and (on my machine) the result is a plan with a merge join.

SELECT * FROM A full outer  JOIN B
on 1=1