無法在計算列上創建過濾索引

在我的上一個問題中，在向表中添加新的計算列時禁用鎖升級是個好主意嗎？，我正在創建一個計算列：

ALTER TABLE dbo.tblBGiftVoucherItem
ADD isUsGift AS CAST
(
   ISNULL(
       CASE WHEN sintMarketID = 2 
           AND strType = 'CARD'
           AND strTier1 LIKE 'GG%' 
       THEN 1 
       ELSE 0 
       END
   , 0) 
   AS BIT
) PERSISTED;

計算的列是PERSISTED，並且根據computed_column_definition (Transact-SQL)：

堅持

指定數據庫引擎將計算值物理儲存在表中，並在計算列所依賴的任何其他列更新時更新這些值。將計算列標記為 PERSISTED 允許在計算列上創建確定性但不精確的索引。有關詳細資訊，請參閱計算列上的索引。任何用作分區表的分區列的計算列都必須顯式標記為 PERSISTED。當指定 PERSISTED 時，computed_column_expression 必須是確定性的。

但是，當我嘗試在列上創建索引時，出現以下錯誤：

CREATE INDEX FIX_tblBGiftVoucherItem_incl
ON dbo.tblBGiftVoucherItem (strItemNo) 
INCLUDE (strTier3)
WHERE isUsGift = 1;

無法在表“dbo.tblBGiftVoucherItem”上創建篩選索引“FIX_tblBGiftVoucherItem_incl”，因為篩選表達式中的列“isUsGift”是計算列。重寫過濾器表達式，使其不包含此列。

如何在計算列上創建過濾索引？

要麼

有替代解決方案嗎？

不幸的是，從 SQL Server 2014 開始，無法創建Filtered Index過濾器在計算列上的位置（無論它是否被持久化）。

自 2009 年以來一直有一個連接項目開放，所以請繼續投票。也許微軟有一天會解決這個問題。

Aaron Bertrand 有一篇文章介紹了過濾索引的許多其他問題。

儘管您無法在持久列上創建篩選索引，但您可以使用一個相當簡單的解決方法。

作為測試，我創建了一個簡單的表，其中包含一個IDENTITY列，以及一個基於標識列的持久計算列：

USE tempdb;

CREATE TABLE dbo.PersistedViewTest
(
   PersistedViewTest_ID INT NOT NULL
       CONSTRAINT PK_PersistedViewTest
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , SomeData VARCHAR(2000) NOT NULL
   , TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO

然後，我創建了一個基於表的模式綁定視圖，並在計算列上有一個過濾器：

CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID
   , SomeData 
   , TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);

接下來，我在模式綁定視圖上創建了一個聚集索引，它具有持久化儲存在視圖中的值的效果，包括計算列的值：

CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO

在表中插入一些測試數據：

INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
   CROSS JOIN sys.objects o1
   CROSS JOIN sys.objects o2;

在視圖上創建統計項和索引：

CREATE STATISTICS ST_PersistedViewTest_View
ON dbo.PersistedViewTest_View(TestComputedColumn)
WITH FULLSCAN;

CREATE INDEX IX_PersistedViewTest_View_TestComputedColumn
ON dbo.PersistedViewTest_View(TestComputedColumn);

如果查詢優化器確定這樣做是有意義的，現在可以SELECT自動使用持久化視圖對具有持久化列的表執行語句：

SELECT pv.PersistedViewTest_ID
   , pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

上述查詢的實際執行計劃顯示查詢優化器選擇使用持久化視圖返回結果：

您可能已經註意到上述WHERE子句中的顯式轉換。此顯式CONVERT(INT, 26)允許查詢優化器正確使用統計對象來估計查詢將返回的行數。如果我們用 編寫查詢WHERE pv.TestComputedColumn = 26，查詢優化器可能無法正確估計行數，因為 26 實際上被認為是TINY INT; 這可能會導致 SQL Server 不使用持久化視圖。隱式轉換可能非常痛苦，並且始終使用正確的數據類型進行比較和連接是值得的。

當然，使用模式綁定產生的所有標準“陷阱”都適用於上述場景；這可能會阻止在所有情況下使用此解決方法。例如，如果不先從視圖中刪除模式綁定，就無法修改基表。為此，您需要從視圖中刪除聚集索引。

如果您沒有 SQL Server Enterprise Edition，則查詢優化器不會自動將持久視圖用於不使用WITH (NOEXPAND)提示直接引用視圖的查詢。要實現在非企業版版本中使用持久視圖的好處，您需要將上面的查詢重寫為：

SELECT pv.PersistedViewTest_ID
   , pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)

感謝Ian Ringrose指出上述企業版限制，並感謝Paul White的(NOEXPAND)提示。

Paul 的這個答案有一些關於查詢優化器與持久視圖相關的有趣細節。

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