SQL Server 2005 indexing same columns in different order

Question

I have a report that was taking a long time to execute. I ran the database engine tuning advisor and one of the recommendations was to create 2 indexes. However, I noticed that the indexes were the same columns, but in different orders.

Here is the table:

---Locations---
| *LocationID |
| Code        |
| ...more...  |
| DivisionID  |
| RegionID    |
---------------

and the recommendation was to add these 2 indexes

CREATE NONCLUSTERED INDEX [IX_Locations_Region_Loc_Div] ON [dbo].[Locations] 
(
 [RegionID] ASC,
 [LocationID] ASC,
 [DivisionID] ASC
)
INCLUDE ( [Code]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Locations_Loc_Reg_Div] ON [dbo].[Locations] 
(
 [LocationID] ASC,
 [RegionID] ASC,
 [DivisionID] ASC
)
INCLUDE ( [Code]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Why would the SQL Server need to have these 3 columns indexes in different orders when they are only used for an inner join?

SELECT UserID, LocationID FROM [User] u INNER JOIN [Locations] l ON u.LocationID = l.LocationID

Answer 1

It wouldn't for the SQL you've included. There must be other queries it's considering to make that reccomendation.

Answer 2

Was there a where clause on the statement when you ran the tuning engine? I would aslo put an order by clause on the statement and see if that helps the database/tuning engine know how to best align the index.

Answer 3

One way to answer your question is look at the execution plan in Sql Server Management Studio of the query before adding the indexes and then after the indexes. Always interesting if not useful information there.

Answer 4

I'm not sure about 2005, but I know that (at least some) previous versions of SQL Server would only pick up an index to use if the columns in the where clause were in the same order as the columns in the index. Don't like it, but did prove it.

I just noticed that you're talking about joins--back then, I mostly wrote my joins in the where clause rather than using ANSI syntax, so I think it still applies.