I have a persisted computed column on a table which is simply made up concatenated columns, e.g.
CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A VARCHAR(20) NOT NULL, B VARCHAR(20) NOT NULL, C VARCHAR(20) NOT NULL, D DATE NULL, E VARCHAR(20) NULL, Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL );
Comp is not unique, and D is the valid from date of each combination of
A, B, C, therefore I use the following query to get the end date for each
A, B, C (basically the next start date for the same value of Comp):
SELECT t1.ID, t1.Comp, t1.D, D2 = ( SELECT TOP 1 t2.D FROM dbo.T t2 WHERE t2.Comp = t1.Comp AND t2.D > t1.D ORDER BY t2.D ) FROM dbo.T t1 WHERE t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS ORDER BY t1.Comp;
I then added an index to the computed column to assist in this query (and also others):
CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;
The query plan however surprised me. I would have thought that since I have a where clause stating that
D IS NOT NULL and I am sorting by
Comp, and not referencing any column outside of the index that the index on the computed column could be used to scan t1 and t2, but I saw a clustered index scan.
So I forced the use of this index to see if it yielded a better plan:
SELECT t1.ID, t1.Comp, t1.D, D2 = ( SELECT TOP 1 t2.D FROM dbo.T t2 WHERE t2.Comp = t1.Comp AND t2.D > t1.D ORDER BY t2.D ) FROM dbo.T t1 WITH (INDEX (IX_T_Comp_D)) WHERE t1.D IS NOT NULL ORDER BY t1.Comp;
Which gave this plan
This shows that a Key lookup is being used, the details of which are:
Now, according to the SQL-Server documentation:
You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.
So if, as the docs say "the Database Engine stores the computed values in the table", and the value is also being stored in my index, why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Also, why can the query use the index on
t2, but not on
N.B. I have tagged SQL Server 2008 because this is the version that my main problem is on, but I also get the same behaviour in 2012.