SQL_Surfer (10/8/2015)
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and retruns 218362 (2162 * 101) rows, which is correct. But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.Here is what I have so far.
SELECT B.*, A.col1
FROM TableB B
LEFT JOIN TABLE A A
ON 1 = 1
Try adding an index on TABLEA.col1, just to see if the cross join operation will do an index scan instead of a full table scan.
Also try changing the syntax to use an actual "CROSS JOIN" clause instead of "LEFT JOIN". It helps SQL Server construct a better execution plan if explicitly state what you're trying to do rather than tease it with something like "LEFT JOIN ... ON 1 = 1".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho