为什么SQL Server不会使用我的计算列执行索引查找

I am using SQL Server 2008 and it is refusing to perform a seek on my index which covers a computed column.

我正在使用SQL Server 2008,它拒绝对覆盖计算列的索引执行搜索。

My table looks like this:

我的表看起来像这样:

CREATE TABLE Person
{
    Id uniqueidentifier NOT NULL,
    InsertDate datetime NOT NULL,
    PhoneNumber NULL,
    PhoneNumberComparable AS (MakePhoneNumberComparable(PhoneNumber)) PERSISTED,
    ... etc...
}

There is a clustered primary key index on the ID column, and also an index on the InsertDate column.

ID列上有一个聚簇主键索引,还有InsertDate列上的索引。

There is an index on the PhoneNumberComparable computed column like this:

PhoneNumberComparable计算列上有一个索引,如下所示:

CREATE NONCLUSTERED INDEX IX_Person_PhoneNumberComparable ON Person
(
    PhoneNumberComparable ASC
)

The indexes all have up to date statistics.

索引都具有最新的统计信息。

My query looks like this:

我的查询如下所示:

SELECT TOP 20 * FROM Person
WHERE PhoneNumberComparable = @PhoneNumber
ORDER BY InsertDate DESC

By default, SQL Server decides to use the index on InsertDate instead of the index on PhoneNumberComparable, causing very poor performance.

默认情况下,SQL Server决定使用InsertDate上的索引而不是PhoneNumberComparable上的索引,从而导致性能非常差。

If I try to force the phone number index to be used, by adding WITH (INDEX=IX_Person_PhoneNumberComparable) to the query, SQL trys to perform a scan, rather than a seek.

如果我尝试强制使用电话号码索引,通过向查询添加WITH(INDEX = IX_Person_PhoneNumberComparable),SQL会尝试执行扫描而不是搜索。

If I try to use the FORCESEEK query hint, then SQL Server gives me the following error:

如果我尝试使用FORCESEEK查询提示,那么SQL Server会给我以下错误:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

由于此查询中定义的提示,查询处理器无法生成查询计划。重新提交查询而不指定任何提示,也不使用SET FORCEPLAN。

So basically, for some reason SQL Server is refusing to seek my index! Why?

所以基本上,由于某种原因,SQL Server拒绝寻求我的索引!为什么?

EDIT

As per suggestions in the comments, I've simplified the query, but the problem still exists (a scan on the primary key is performed instead of a seek on the phone number index):

根据评论中的建议,我简化了查询,但问题仍然存在(执行主键扫描而不是搜索电话号码索引):

SELECT TOP 20 PhoneNumberComparable  FROM Person
WHERE PhoneNumberComparable = @PhoneNumber

1 个解决方案

#1

I believe I have figured this out.

我相信我已经弄明白了。

The problem is due to the MakePhoneNumberComparable function making use of another function that is under a different schema. To fix the problem I had to clone a second copy of the other function but move it under the same schema as the table.

问题是由于MakePhoneNumberComparable函数使用了另一个模式下的另一个函数。要解决这个问题,我必须克隆另一个函数的第二个副本,但是在与表相同的模式下移动它。

This article (thanks to commenter Kahn) says that you can only define indexes if "All function references in the computed column have the same owner as the table".

本文(感谢评论者Kahn)说,如果“计算列中的所有函数引用与表具有相同的所有者”,则只能定义索引。

Well, not only is this ownership requirement very annoying to adhere to, but the documentation from Microsoft is very confusing to say the least:

好吧,这个所有权要求不仅非常令人讨厌,而且微软的文档至少可以说是令人困惑的:

Firstly, I could create an index. In fact I could even scan the index. I just couldn't get SQL to perform a seek on the index.

首先,我可以创建一个索引。事实上,我甚至可以扫描索引。我只是无法让SQL对索引执行搜索。

Secondly, as far as I am aware, we are talking about schemas here, not owners, but on the difference between the two I'm still somewhat confused about.

其次,据我所知,我们在这里讨论架构,而不是所有者,但两者之间的差异我仍然有点困惑。

Thirdly, my function was in the same schema as my table - it just made use of second function that wasn't in the same schema as the table.

第三,我的函数与我的表在同一模式中 - 它只使用了与表不在同一模式中的第二个函数。

#1