持久計算列上的索引不可搜尋
我有一個名為 的表
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)
) 的事實有關。我也遇到了這個更簡單版本的問題(更改
c1
to的定義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 的解決方法的一個版本。隨意投票。