Index on a persisted computed column not seekable

Question

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, returning the primary key:

SELECT @ADDRESSID= ISNULL(AddressId,0)
FROM dbo.[Address]
WHERE HashKey = @HashKey

I get this plan:

If I force the index I get this even worse plan:

If I try and force both the index and a seek, I get an 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

Is this just because it's not precise? I thought that didn't matter if it was persisted?

Is there a way to make this index seekable without making this a non-computed column?

Does anyone have any links to information on this?

I can't post the actual table creation, but here is a test table that has the same issue:

drop TABLE [dbo].[Test]

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA', 
                                ( RIGHT(REPLICATE(' ', (100)) + isnull([test], ''), ( 100 )) ) 
                                + RIGHT(REPLICATE(' ', (100)) + isnull([TestGeocode].[ToString](), ''), ( 100 ))
                            ) 
                        ) AS BINARY(20)                                                                                                        
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )    
GO    
DECLARE @Hashkey BINARY(20)

SELECT [Hashkey]
FROM   [dbo].[Test] WITH (FORCESEEK) /*Query processor could not produce a query plan*/
WHERE  [Hashkey] = @Hashkey 

Answer 1

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)).

I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it)

DROP TABLE dbo.Test

CREATE TABLE dbo.Test
  (
     c1        VARCHAR(
                          MAX    --Fails
                        --  8000 --Works fine
                          ) NULL,
     comp1 AS CAST(ISNULL(c1, 'ABC') AS VARCHAR(100))
    CONSTRAINT UK_Test_comp1 UNIQUE NONCLUSTERED(comp1)
  )

GO

DECLARE @comp1 VARCHAR(100)

SELECT comp1
FROM   dbo.Test WITH (FORCESEEK)
WHERE  comp1 = @comp1 
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606); 

Computed column references get expanded out to the underlying definition then matched back to the column later. This allows computed columns to be matched without referencing them by name at all and also allows simplification to operate on the underlying definitions.

ISNULL returns the datatype of the first parameter (VARCHAR(MAX) in my example). The return type of COALESCE will be VARCHAR(MAX) here too but it seems to be evaluated differently in a way that avoids the problem.

In the cases where the query succeeds the trace flag output includes the following

ScaOp_Convert varchar(max) collate 49160,Null,Var,Trim,ML=65535

    ScaOp_Const TI(varchar collate 49160,Var,Trim,ML=3) 
                      XVAR(varchar,Owned,Value=Len,Data = (3,ABC))

Where it fails this is replaced by

ScaOp_Identifier COL: ConstExpr1003 

I speculate that in the cases where it fails the (implicit) CAST('ABC' AS VARCHAR(MAX)) is just done once and this is evaluated as a runtime constant (more information). However the reference to this runtime constant label, instead of the actual string literal value itself, prevents it from matching the computed column definition.

This rewrite avoids the issue in your query

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA', 
                                ( RIGHT(SPACE(100) + isnull([test], ''), 100) ) 
                                + RIGHT(SPACE(100) + isnull(CAST(RIGHT([TestGeocode].[ToString](),100) AS VARCHAR(100)), ''),100)
                            ) 
                        ) AS BINARY(20)                                                                                                        
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )

Answer 2

You will get these symptoms due to a non-sargable expression if the data type of @HashKey doesn't match that of the indexed column. You might need an explicit CAST in the computed column expression to coerce the desired data type.

Based on your repro, I suspect this is a bug. I filed a Connect bug, Computed Column Index Not Used, with a version of Martin's workaround too. Feel free to vote.