Why SQL Server does not search the index using my calculated column

Question

I am using SQL Server 2008 and refuse to search on my index that spans the computed column.

My table looks like this:

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

The ID column contains the index of the cluster primary key, as well as the index in the InsertDate column.

The calculated column PhoneNumberComparable has an index:

 CREATE NONCLUSTERED INDEX IX_Person_PhoneNumberComparable ON Person ( PhoneNumberComparable ASC ) 

All indexes have updated 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 in InsertDate instead of the index on PhoneNumberComparable, which results in very poor performance.

If I try to get the phone number index to be used by adding WITH WITH (INDEX = IX_Person_PhoneNumberComparable) to the query, SQL will try to check, not search.

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

The query processor was unable to create a query plan due to the prompts defined in this query. Repeat the query without prompting and without using SET FORCEPLAN.

So, for some reason, SQL Server refuses to search my index! Why?

EDIT

As suggested in the comments, I simplified the request, but the problem still exists (scanning the primary key is performed instead of searching by phone number index):

 SELECT TOP 20 PhoneNumberComparable FROM Person WHERE PhoneNumberComparable = @PhoneNumber 

Answer 1

I believe that I understood this.

The problem is with the MakePhoneNumberComparable function, which uses another function that is under a different scheme. To fix this problem, I had to clone a second copy of another function, but move it along the same lines as the table.

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

Well, not only is this ownership requirement very annoying, but Microsoft's documentation is very confusing:

• First, I could create an index. In fact, I could even scan the index. I simply could not get SQL to search by index.
• Secondly, as far as I know, we are talking about the schemes here, and not about the owners, but I'm still a little confused about the difference between them.
• Thirdly, my function was in the same schema as my table — it just used a second function that was not in the same schema as the table.