Indeks trwałej kolumny obliczeniowej nie jest widoczny

Mam tabelę o nazwie Address, która ma utrwaloną kolumnę obliczeniową o nazwie Hashkey. Kolumna jest deterministyczna, ale nieprecyzyjna. Ma unikalny indeks, którego nie można zobaczyć. Jeśli uruchomię to zapytanie, zwracam klucz podstawowy:

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

Dostaję ten plan:

Jeśli wymuszę indeks, otrzymam ten jeszcze gorszy plan:

Jeśli spróbuję wymusić indeks i wyszukiwanie, pojawia się błąd:

Procesor zapytań nie mógł wygenerować planu zapytania ze względu na wskazówki zdefiniowane w tym zapytaniu. Ponownie wprowadź zapytanie bez podawania wskazówek i bez użyciaSET FORCEPLAN

Czy to tylko dlatego, że nie jest precyzyjne? Pomyślałem, że to nie ma znaczenia, czy to się utrzyma?

Czy istnieje sposób, aby ten indeks był widoczny bez zmieniania go w kolumnę nieobliczoną?

Czy ktoś ma jakieś linki do informacji na ten temat?

Nie mogę opublikować faktycznego utworzenia tabeli, ale oto tabela testowa, która ma ten sam problem:

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 

Problem wydaje się być związany z tym, że [TestGeocode].[ToString]()zwraca maxtyp danych ( nvarchar(max)).

Mam również problem z tą prostszą wersją (zmiana definicji c1na varchar(8000)lub używanie COALESCEzamiast ISNULLrozwiązania)

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

Obliczone odwołania do kolumn są rozszerzane do definicji podstawowej, a następnie dopasowywane z powrotem do kolumny później. Pozwala to na dopasowanie kolumn obliczeniowych bez odwoływania się do nich po nazwie, a także umożliwia uproszczenie działania na podstawowych definicjach.

ISNULLzwraca typ danych pierwszego parametru ( VARCHAR(MAX)w moim przykładzie). Rodzaj zwrotu również COALESCEbędzie VARCHAR(MAX)tutaj, ale wydaje się, że jest oceniany inaczej w sposób pozwalający uniknąć problemu.

W przypadku powodzenia zapytania wyjście flagi śledzenia zawiera następujące elementy

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

W przypadku niepowodzenia zastępuje się go

ScaOp_Identifier COL: ConstExpr1003 

I spekulują, że w przypadkach, gdy nie powiedzie się (implicite) CAST('ABC' AS VARCHAR(MAX))jest po prostu zrobić raz i to jest oceniane jako stała wykonawczego ( więcej informacji ). Jednak odwołanie do tej etykiety stałej wykonawczej zamiast samej rzeczywistej wartości literału łańcucha zapobiega dopasowaniu obliczonej definicji kolumny.

To przepisanie pozwala uniknąć problemu w zapytaniu

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

Pojawią się te objawy z powodu niewymiernego wyrażenia, jeśli typ danych @HashKeynie jest zgodny z typem kolumny indeksowanej. Może być potrzebny wyraźny element CASTw wyliczonym wyrażeniu kolumny, aby wymusić pożądany typ danych.

W oparciu o twoje repro podejrzewam, że to błąd. Złożyłem błąd Connect, Computed Column Index Not Used , wraz z wersją obejścia Martina. Głosuj swobodnie.