如何通過視圖獲取 SEEK 訪問轉換後的 ID

假設我有一張桌子：

-- just for test purposes
CREATE TABLE SomeTable (
   ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
   ,SomeColumn1 NVARCHAR(50) NULL
   ,SomeColumn2 DATETIME NULL
   );

-- populate table with some rows
INSERT INTO SomeTable DEFAULT VALUES;
GO 1000

因為第三方應用程序有一個視圖將表的 ID 列從轉換INT為NVARCHAR（假設這是必須的）：

CREATE VIEW ThirdPartyView AS
SELECT
   ID = CAST(ID as NVARCHAR(10))
   ,C1 = SomeColumn1
   ,C2 = SomeColumn2
FROM SomeTable;

然後當我通過 ID 訪問一行時，我得到一個 INDEX SCAN：

SELECT *
FROM ThirdPartyView
WHERE ID = N'1'

我明白為什麼。

我該怎麼做才能在查詢之外獲得 INDEX SEEK？

社論/情況：

• 無法更改基表 (SomeTable) 定義（無法添加列）
• 視圖必須具有相同的列（不能添加列）
• 定義任何索引是可能的
• 我可以使視圖成為索引視圖，但更願意避免使用該選項
• 對方期望 ID 列的數據類型是NVARCHAR. 不幸的是，我必須滿足這個條件。或者我被告知要見他們。如果不是，他們的應用程序可能會失敗。

問題是您通過視圖進行的查詢與執行的查詢相同

SELECT *
FROM SomeTable
WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1'

謂詞中的幾乎任何CAST一列都會使該謂詞不可分割。我知道的唯一例外是在某些排序規則下CAST的to和todatetime列。date``VARCHAR``NVARCHAR

NVARCHARto也不例外INT。

你可能希望它會做類似的事情

WHERE SomeTable.ID = TRY_CAST('Your search string' as int)

但這並不是那麼簡單。如果您的搜尋字元串是'1'這兩個將返回相同的結果，但對於搜尋字元串'¹'（上標 1），轉換int失敗，但字元串比較在某些排序規則下比較相等。相反，對於搜尋字元串' 1'（帶有前導空格），強制轉換int和比較將丟棄前導空格並比較相等，但字元串比較將比較不相等（與0轉換為時轉換為的空字元串類似int）

可能的解決方案

通常，您可以在引用計算列的基表上創建索引視圖或新索引，但與CAST從視圖中刪除相比，兩者似乎都不是最佳的，因此可以查找現有索引。

計算列

SomeTable您可以使用定義創建一個計算列CAST(ID as NVARCHAR(10))，然後對其進行索引。

ALTER TABLE SomeTable ADD strID AS CAST(ID as NVARCHAR(10));    

CREATE INDEX IX ON SomeTable (strID ) INCLUDE (ID, SomeColumn1, SomeColumn2);

SELECT *
FROM ThirdPartyView
WHERE ID = N'1';

索引視圖

鑑於問題中的限制，計算列的想法似乎被排除在外。另一種方法是創建索引視圖，但您可能需要更改查詢文本才能使其正常工作。

• 在除企業版之外的所有版本中，NOEXPAND都需要提示來匹配索引視圖。
• 如果您使用的是企業版，則無論您索引原始視圖還是創建副本，原則上自動匹配都可以工作……
• …但索引視圖匹配只會在優化的後期考慮。如果查詢足夠便宜，優化將在到達該點之前結束。特別是在您的情況下，您還需要與瑣碎的計劃作鬥爭。我向表中添加了一百萬行數據，但索引視圖仍未命中 - 因為該計劃低於並行性的成本門檻值，並且它沒有繼續進行進一步的優化階段，而不是微不足道的。

你為什麼不改變 3rd 方視圖，或者重命名它並創建你自己的，這樣它就不會執行這個愚蠢的轉換？如果您可以在不違反支持協議的情況下向視圖添加索引，那麼您當然可以更改視圖的定義。

ALTER VIEW dbo.ThirdPartyView
AS
SELECT ID
   ,C1 = SomeColumn1
   ,C2 = SomeColumn2
FROM SomeTable;

由於數據類型優先規則，您的查詢不必更改即可進行搜尋，但無論如何您都應該更改它：

SELECT ID, C1, C2
FROM dbo.ThirdPartyView
WHERE ID = N'1';

計劃：

如果應用程序絕對需要ID將視圖中列的數據類型報告為NVARCHAR（可能出於綁定目的），您可以進一步修改視圖定義，以便ID在選擇列表中定義為CAST(ID AS NVARCHAR(10)) AS ID：

ALTER VIEW dbo.ThirdPartyView
AS
SELECT
   ID = CONVERT(nvarchar(10), ID),
   C1 = SomeColumn1,
   C2 = SomeColumn2
FROM SomeTable;

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