辨識具有任意數量鍵列的記錄的最有效方法

我使用大型第三方數據集。長期的經驗告訴我，一旦傳入的數據行進入我的系統，就給它一個代理 ID 是一個非常好的主意，這樣我就可以在它被驗證、入庫等時輕鬆跟踪它。問題是關鍵值可能潛在是每個維度值，可以是 200 列。

我的一般流程是這樣的：

1. 將數據載入到臨時表。
2. 將數據與僅包含鍵值和代理 IDS 的第二個 IdMatch 表匹配。

IF OBJECT_ID('Staging.myTest') IS NULL
   CREATE TABLE Staging.myTest (
       [ID] INT IDENTITY(1,1) NOT NULL,
       [Hash] INT NULL,
       [Dim_1] NVARCHAR(32) NOT NULL,
       [Dim_2] NVARCHAR(32) NOT NULL,
       [Dim_3] NVARCHAR(32) NULL,
       [Met_1] INT NULL,
       [Met_2] DECIMAL(5,2) NULL
   );

IF OBJECT_ID('IdMatch.myTest') IS NULL
   CREATE TABLE IdMatch.myTest (
       [ID] INT IDENTITY(1,1) NOT NULL,
       [Hash] INT NULL,
       [Dim_1] NVARCHAR(32) NOT NULL,
       [Dim_2] NVARCHAR(32) NOT NULL,
       [Dim_3] NVARCHAR(32) NULL,
   );

TRUNCATE TABLE Staging.myTest;
TRUNCATE TABLE IdMatch.myTest;

INSERT INTO Staging.myTest
   ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A'),
   ('B', 'B', 'B'),
   ('C', 'C', NULL),
   ('C', 'C', 'C'),
   ('D', 'D', 'D');

INSERT INTO IdMatch.myTest
   ([Dim_1], [Dim_2], [Dim_3])
VALUES ('A', 'A', 'A');

--My Proc (as script) for setting the index.
INSERT INTO [IdMatch].myTest
   ([Dim_1], [Dim_2], [Dim_3])
SELECT src.[Dim_1], src.[Dim_2], src.[Dim_3]
FROM Staging.myTest AS src
WHERE NOT EXISTS (
       SELECT tgt.[Dim_1], tgt.[Dim_2], tgt.[Dim_3]
       FROM [IdMatch].myTest AS tgt
       WHERE tgt.[Dim_1] = src.[Dim_1]
           AND tgt.[Dim_2] = src.[Dim_2]
           AND tgt.[Dim_3] = src.[Dim_3]
   );

SELECT * FROM IdMatch.myTest

問題：當實際數據集包含 200 多列 NVARCHAR 數據時，以這種方式匹配獲取代理 ID 需要很長時間。有沒有更好的辦法？我已經嘗試過預先計算雜湊，但不確定如何處理我將產生的最終衝突。

一種常見的方法是選擇一個具有非常非常小的衝突機會的雜湊函式，然後假設不會有任何衝突：

CREATE TABLE Staging.myTest (
   [ID] INT IDENTITY(1,1) NOT NULL,
   [Hash] AS 
       CONVERT(binary(32), 
           HASHBYTES('SHA2_256', 
               CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
   [Dim_1] NVARCHAR(32) NOT NULL,
   [Dim_2] NVARCHAR(32) NOT NULL,
   [Dim_3] NVARCHAR(32) NULL,
   [Met_1] INT NULL,
   [Met_2] DECIMAL(5,2) NULL
);
GO
CREATE TABLE IdMatch.myTest (
   [ID] INT IDENTITY(1,1) NOT NULL,
   [Hash] AS 
       CONVERT(binary(32), 
           HASHBYTES('SHA2_256', 
               CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
   [Dim_1] NVARCHAR(32) NOT NULL,
   [Dim_2] NVARCHAR(32) NOT NULL,
   [Dim_3] NVARCHAR(32) NULL,
);
GO
-- Declared unique because we have decided it will be
CREATE UNIQUE NONCLUSTERED INDEX 
   IX_HASH 
ON IdMatch.myTest 
   ([Hash]);

注意：大多數人用空字元串替換列 NULL 以進行散列，這是CONCAT. 如果您需要區分 NULL 和空字元串，則需要確定要使用的其他一些魔法值，並用ISNULLor包裹可空列COALESCE。

然後添加不匹配的行：

INSERT Staging.myTest
(
   Dim_1, 
   Dim_2, 
   Dim_3
)
SELECT
   SRC.Dim_1,
   SRC.Dim_2,
   SRC.Dim_3
FROM Staging.myTest AS SRC
WHERE
   NOT EXISTS
   (
       SELECT 1
       FROM IdMatch.myTest AS TGT
       WHERE
           TGT.[Hash] = SRC.[Hash]
   );

請參閱Greg Low的查找 T-SQL 中已更改的行 – CHECKSUM、BINARY_CHECKSUM、HASHBYTES。你應該用你的數據來測試這個方法，看看這個方案是否適合你。

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