Why won't SQL Server perform an index seek using my computed column
sql-server
Solution
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.
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".
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.
- 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.
Problem
I am using SQL Server 2008 and it is refusing to perform a seek on my index which covers a 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... } ``` There is a clustered primary key index on the ID column, and also an index on the InsertDate column. There is an index on the PhoneNumberComparable computed column like this: ``` 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. 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. If I try to use the FORCESEEK query hint, then SQL Server gives me the following error: 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. So basically, for some reason SQL Server is refusing to seek my index! Why? 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 ```