CODEWITHSUNDEEP
sql-server-2005t-sqlindexing
xr280xr
xr280xr

Reputation: 13302

Is the addition of a second ID column beneficial to index?

Let's say I have a table tbl_FacilityOrders with two foreign keys fk_FacilityID and fk_OrderID in SQL Server 2005. It could contain orders from a few hundred facilities. I need to query single records and will have both the facilityID and the orderID available to me. Is it better to define an index on fk_FacilityID then fk_OrderID and pass the both to the query or to just use fk_OrderID. Since there will be less facility IDs than order IDs, I could see weeding out the other facilities' records first possibly being beneficial.

A second question is, if I were using the two columnn query above, does the order I write my WHERE clause columns in matter or is is the engine smart enough to evaluate them in the order of the index?

E.G. Is:

WHERE fk_facilityID = @FacilityID AND fk_OrderID = @OrderID

equivalent to:

WHERE fk_OrderID = @OrderID AND fk_FacilityID = @FacilityID

?

Upvotes: 1

Views: 56

Answers (2)

JNK
JNK

Reputation: 65157

Is it better to define an index on fk_FacilityID then fk_OrderID and pass the both to the query or to just use fk_OrderID.

If OrderId is unique, there's no real added benefit to adding the other field for the scenario given. It is a good idea to index your FKs, though, since they will always been a JOIN key.

if I were using the two columnn query above, does the order I write my WHERE clause columns in matter or is is the engine smart enough to evaluate them in the order of the index?

Nope, order is irrelevant here. All that matters is that the SETS of fields match, i.e. FieldA and FieldB are both in the index and in the WHERE clause.

The order of fields in the index DOES matter, though. You can't use the second field in an index without knowing the value of the first field.

Upvotes: 7

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

You should create an index for each of your foreign keys... not just the purpose of this question, but because indexing your foreign keys is good practice in general.

To answer your second question, the two statements are equivalent. SQL Server should internally re-order the statements to arrive at the optimal execution plan... however, you should always validate the generated execution plan just to make sure that its behaving as you would expect.

Upvotes: 2

Related Questions