持久計算列上的索引不可搜尋

我有一個名為 的表Address，它有一個名為 的持久計算列Hashkey。該列是確定性的，但不精確。它有一個不可搜尋的唯一索引。如果我執行這個查詢，返回主鍵：

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

我得到這個計劃：

如果我強制索引，我會得到這個更糟糕的計劃：

如果我嘗試同時強制索引和搜尋，我會得到一個錯誤：

由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。重新送出查詢而不指定任何提示並且不使用SET FORCEPLAN

這僅僅是因為它不精確嗎？我以為堅持下去沒關係？

有沒有辦法讓這個索引在不使其成為非計算列的情況下成為可搜尋的？

有沒有人有任何連結到這方面的資訊？

我無法發布實際的表創建，但這是一個具有相同問題的測試表：

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 

該問題似乎與[TestGeocode].[ToString]()返回max數據類型 ( nvarchar(max)) 的事實有關。

我也遇到了這個更簡單版本的問題（更改c1to的定義varchar(8000)或使用COALESCE而不是ISNULL解決它）

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

計算的列引用擴展到基礎定義，然後稍後匹配回列。這允許匹配計算列而根本不按名稱引用它們，並且還允許簡化對基礎定義的操作。

ISNULL返回第一個參數的數據類型（VARCHAR(MAX)在我的範例中）。的返回類型也COALESCE將在VARCHAR(MAX)這裡，但它似乎以不同的方式評估以避免問題。

在查詢成功的情況下，跟踪標誌輸出包括以下內容

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

如果失敗，則替換為

ScaOp_Identifier COL: ConstExpr1003 

我推測在失敗的情況下（隱式）CAST('ABC' AS VARCHAR(MAX))只執行一次，這被評估為執行時常量（更多資訊）。然而，對這個執行時常量標籤的引用，而不是實際的字元串文字值本身，會阻止它匹配計算的列定義。

此重寫避免了查詢中的問題

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

@HashKey如果 的數據類型與索引列的數據類型不匹配，您將因非 sargable 表達式而出現這些症狀。您可能需要CAST在計算列表達式中顯式地強制轉換所需的數據類型。

根據您的複制，我懷疑這是一個錯誤。我送出了一個 Connect 錯誤，Computed Column Index Not Used以及 Martin 的解決方法的一個版本。隨意投票。

引用自：https://dba.stackexchange.com/questions/107444