April 22, 2008 at 8:27 am
Scenario
Table 1: small tall table with an ID_field and state, with clustered index on ID_field
Table 2: Large wide table with id_field and many columns, with index on ID_field (cannot be cluster indexes because of size).
Each table has the same number of records and same set of ID_fields.
Question:
When I join the two tables via a SELECT INTO statement is it possible to have the records in the new table be sorted according to the clustered index on table 1?
I wonder whether it matters when the join condition is:
--> from Table1 a left join Table2 b on a.ID_field = b.id_field
--> from Table2 b left join Table1 a on b.ID_field = a.id_field
Thanks!
April 22, 2008 at 10:47 am
While the optimizer might choose to sort & join using the index on the first table, it can't use the index on the first table to access the data on the second table. It just can't. The statistics and pointers inside the index are for the first table, not the second. It will have to have do a scan against the second table or have an index to use, but it will be filled with data about the second table, not the first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply