sql server - Index on a persisted computed column not seekable - Database Administrators Stack Exchange

i have table, called address, has persisted computed column called hashkey. column deterministic not precise. has unique index on not seekable. if run query, returning primary key:

select @addressid= isnull(addressid,0) dbo.[address] hashkey = @hashkey 

i plan:

if force index worse plan:

if try , force both index , seek, error:

query processor not produce query plan because of hints defined in query. resubmit query without specifying hints , without using set forceplan

is because it's not precise? thought didn't matter if persisted?

is there way make index seekable without making non-computed column?

does have links information on this?

i can't post actual table creation, here test table has same issue:

drop table [dbo].[test]  create table [dbo].[test]   (      [test]        [varchar](100) null,      [testgeocode] [geography] null,      [hashkey] cast(                         ( hashbytes                             ('sha',                                  ( right(replicate(' ', (100)) + isnull([test], ''), ( 100 )) )                                  + right(replicate(' ', (100)) + isnull([testgeocode].[tostring](), ''), ( 100 ))                             )                          ) binary(20)                                                                                                                               ) persisted     constraint [uk_test_hashkey] unique nonclustered([hashkey])   )     go     declare @hashkey binary(20)  select [hashkey]   [dbo].[test] (forceseek) /*query processor not produce query plan*/  [hashkey] = @hashkey  

the problem seems related fact [testgeocode].[tostring]() returns max datatype (nvarchar(max)).

i encounter issue simpler version (changing definition of c1 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 cast(isnull(c1, 'abc') varchar(100))     constraint uk_test_comp1 unique nonclustered(comp1)   )  go  declare @comp1 varchar(100)  select comp1   dbo.test (forceseek)  comp1 = @comp1  option (querytraceon 3604, querytraceon 8606);  

computed column references expanded out underlying definition matched column later. allows computed columns matched without referencing them name @ , allows simplification operate on underlying definitions.

isnull returns datatype of first parameter (varchar(max) in example). return type of coalesce varchar(max) here seems evaluated differently in way avoids problem.

in cases query succeeds trace flag output includes 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 fails replaced by

scaop_identifier col: constexpr1003  

i speculate in cases fails (implicit) cast('abc' varchar(max)) done once , evaluated runtime constant (more information). reference runtime constant label, instead of actual string literal value itself, prevents matching computed column definition.

this rewrite avoids issue in query

create table [dbo].[test]   (      [test]        [varchar](100) null,      [testgeocode] [geography] null,      [hashkey] cast(                         ( hashbytes                             ('sha',                                  ( right(space(100) + isnull([test], ''), 100) )                                  + right(space(100) + isnull(cast(right([testgeocode].[tostring](),100) varchar(100)), ''),100)                             )                          ) binary(20)                                                                                                                               ) persisted     constraint [uk_test_hashkey] unique nonclustered([hashkey])   )