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
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