SQL Server中列哈希实现及表内列重复检测技术问询

Hey there! Let's tackle your two SQL Server challenges head-on—generating a row-order-aware hash/checksum for entire columns, and verifying if one column duplicates another (or multiple others) based on all row values in sequence.

First off, a critical note: SQL tables don’t have a built-in "natural" row order. To get a reliable hash that reflects the exact sequence of values in your column, you must have a sort key—like an auto-incrementing ID column, a creation timestamp, or any column that definitively defines the order of rows. Without this, your hash will be inconsistent and meaningless.

Here’s the most reliable approach using HASHBYTES (cryptographically secure) and STRING_AGG (to concatenate values in order):

-- Swap out YourTable, ID, and ColA with your actual table, sort key, and column
SELECT 
    HASHBYTES('SHA2_256', 
        STRING_AGG(
            ISNULL(ColA, '<<NULL_MARKER>>'), -- Explicitly handle NULLs (STRING_AGG ignores them by default)
            '|' -- Use a delimiter that NEVER appears in your varchar values to avoid ambiguity
        ) WITHIN GROUP (ORDER BY ID) -- This clause enforces the row order—don’t skip it!
    ) AS Column_Level_SHA256_Hash
FROM YourTable;

Quick breakdown: #

• ISNULL(ColA, '<<NULL_MARKER>>'): Ensures NULL values are treated as a unique value, so they don’t get ignored and throw off your hash.
• Delimiter choice: If your data uses |, pick something else like ### or even CHAR(0) (a non-printable character) to prevent value collisions.
• SHA2_256: Far less likely to produce hash collisions than older algorithms like MD5. If you need speed over cryptographic safety, use a checksum approach—but beware of collision risks:

-- Faster but less reliable checksum (use only for non-critical checks)
SELECT 
    CHECKSUM(
        STRING_AGG(ISNULL(ColA, '<<NULL_MARKER>>'), '|') WITHIN GROUP (ORDER BY ID)
    ) AS Ordered_Column_Checksum
FROM YourTable;

You’ve got two options here, depending on whether you just need a yes/no answer or want to see exactly which rows differ.

Method 1: Compare Column Hashes (Fast for Large Tables) #

If the hash values of two columns match, you can be 99.999% sure they’re identical in row order (collisions with SHA2_256 are practically impossible for most use cases):

DECLARE @HashColA VARBINARY(32), @HashColB VARBINARY(32);

-- Grab the hash for Column A
SELECT @HashColA = HASHBYTES('SHA2_256', 
    STRING_AGG(ISNULL(ColA, '<<NULL_MARKER>>'), '|') WITHIN GROUP (ORDER BY ID))
FROM YourTable;

-- Grab the hash for Column B
SELECT @HashColB = HASHBYTES('SHA2_256', 
    STRING_AGG(ISNULL(ColB, '<<NULL_MARKER>>'), '|') WITHIN GROUP (ORDER BY ID))
FROM YourTable;

-- Compare and output the result
SELECT 
    CASE 
        WHEN @HashColA = @HashColB THEN 'ColA and ColB are identical (row-order matched)'
        ELSE 'ColA and ColB differ in one or more rows' 
    END AS Column_Comparison_Result;

Method 2: Row-by-Row Comparison (Find Exact Mismatches) #

If you need to pinpoint exactly which rows don’t match, run a direct row-level comparison:

SELECT 
    ID,
    ColA,
    ColB,
    CASE 
        WHEN ColA <> ColB OR (ColA IS NULL <> ColB IS NULL) THEN 'Mismatch'
        ELSE 'Match' 
    END AS Row_Status
FROM YourTable
WHERE 
    ColA <> ColB 
    OR (ColA IS NULL AND ColB IS NOT NULL)
    OR (ColA IS NOT NULL AND ColB IS NULL);

If this query returns no rows, ColA and ColB are identical in row order.

Comparing One Column to Multiple Columns #

To check if your target column matches any of several other columns, use dynamic SQL to automate the comparisons:

DECLARE @TargetColumn NVARCHAR(128) = 'ColA';
DECLARE @CompareColumns NVARCHAR(MAX) = 'ColB,ColC,ColD'; -- List columns to compare against
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
DECLARE @TargetHash VARBINARY(32);
SELECT @TargetHash = HASHBYTES(''SHA2_256'', STRING_AGG(ISNULL(' + QUOTENAME(@TargetColumn) + ', ''<<NULL_MARKER>>''), ''|'') WITHIN GROUP (ORDER BY ID))
FROM YourTable;

SELECT 
    Column_Name = cmp_col,
    Match_Status = CASE 
        WHEN HASHBYTES(''SHA2_256'', STRING_AGG(ISNULL(' + QUOTENAME(@TargetColumn) + ', ''<<NULL_MARKER>>''), ''|'') WITHIN GROUP (ORDER BY ID)) = @TargetHash 
        THEN ''Matches ' + @TargetColumn + '''
        ELSE ''Does not match ' + @TargetColumn + '''
    END
FROM YourTable
CROSS APPLY (VALUES (' + REPLACE(@CompareColumns, ',', '),(') + ')) AS cmp(cmp_col)
GROUP BY cmp_col;';

EXEC sp_executesql @SQL;

内容的提问来源于stack exchange，提问作者Markian Zadony