SQL Server 2005以不同的顺序索引相同的列

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.

我的报告需要很长时间才能执行。我运行了数据库引擎优化顾问,其中一个建议是创建2个索引。但是,我注意到索引是相同的列,但顺序不同。

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?

当SQL Server仅用于内连接时,为什么SQL Server需要以不同的顺序包含这3列索引?

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

4 个解决方案

#1

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

它不适用于您包含的SQL。它正在考虑进行其他查询以进行推荐。

#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.

运行调优引擎时,语句中是否有where子句?我会在语句中放置一个order by子句,看看是否有助于数据库/调优引擎知道如何最好地对齐索引。

#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.

回答问题的一种方法是在添加索引之前,然后在索引之后查看查询的Sql Server Management Studio中的执行计划。如果没有有用的信息,总是很有趣。

#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.

我不确定2005年,但我知道(至少有一些)SQL Server的早期版本只会选择要使用的索引,如果where子句中的列与索引中的列的顺序相同。不喜欢它,但确实证明了这一点。

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.

我刚刚注意到你在谈论连接 - 那时候,我主要是在where子句中编写我的连接而不是使用ANSI语法,所以我认为它仍然适用。

#1