Have Geek Brain

SQL Server 2005 indexes the same columns in a different order

I have a report that is taking a long time. 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 a table:

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

and the recommendation was to add these 2 indices

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 does SQL Server have to have these 3 index columns in different orders when used for inner join only?

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


a source to share


4 answers


This is not for the SQL you have included. There must be other requests he is considering to make this recommendation.



+2


a source


Was there a where clause in the statement when you ran the customization engine? I would also put an order by clause in the statement and see if that helps so the database / setup engine knows how to best align the index.



0


a source


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

0


a source


I'm not sure about 2005, but I know that (at least some) previous versions of SQL Server could only get an index to use if the columns in the where clause were in the same order as the columns in the index column. I don't like it, but I proved it.

I just noticed that you are talking about joins - then I basically wrote my joins in a where clause instead of using ANSI syntax, so I guess it still applies.

0


a source






More articles:


All Articles