CODEWITHSUNDEEP
sqlsql-servert-sqlindexingperformance
SEA_TRV
SEA_TRV

Reputation:

SQL Server Index question

I have a query that joins 3 tables in SQL Server 2005, but has no Where clause, so I am indexing the fields found in the join statement.

If my index is set to Col1,col2,col3

And my join is

Tbl1
inner join tbl2
On
Tbl1.col3=tbl2.col3
Tbl1.col2=Tbl2.col2
Tbl1.col1=Tbl2.col1

Does the order of the join statement make a difference as compared to the order of the index? Should I set my index to be Col3,col2,col1? Or rearrage my join statement to be Col1,col2,col3?

Thanks

Upvotes: 3

Views: 280

Answers (3)

mjv
mjv

Reputation: 75125

For querying purposes, it does not matter. You may consider alternate ordering sequences, based on the following:

  • possible use of the index for other queries (including some with ORDER BY .. one of these columns)
  • to limit index fragmentation (by using orders that tends to add records towards the end of the table and/or nearby non-selective parameters)

Edit: on second thoughts, having the most selective column first may help the optimizer, for example by providing it with a better estimated row yield and such... But this important issue may be getting off topic, as the OP's question was whether the order of the join conditions mattered.

Upvotes: 1

Heiko Hatzfeld
Heiko Hatzfeld

Reputation: 3197

If you allways have a join on Col1-3 then you should build the index so that the "most distinctive column" is in the 1st field and the most general ones in the last field

So a "Status Ok" / "Status denied" field should be field 3 and a SSN or Phonenumber should be field one on the index

Upvotes: 1

gbn
gbn

Reputation: 432271

The SQL Server query optimiser should work it out. No need to change for the example you gave.

This is the simple answer though, and it depends on what columns you are selecting and how you are joining the 3 tables.

Note: I'd personally prefer to change the JOIN around to match a "natural" order. That is, I try to use my columns in the same order (JOIN, WHERE) that matches my keys and/or indexes. As Joel mentioned, it can help later on for troubleshooting.

Upvotes: 5

Related Questions