我的部分工作负载使用了一个CLR 函数，该函数实现了诡异的哈希算法来比较行以查看是否有任何列值发生了变化。CLR 函数将二进制字符串作为输入，因此我需要一种将行转换为二进制字符串的快速方法。我希望在整个工作负载期间散列大约 100 亿行，所以我希望这段代码尽可能快。
我有大约 300 个不同模式的表。出于这个问题的目的，请假设一个包含 32 个可空INT列的简单表结构。我在这个问题的底部提供了示例数据以及对结果进行基准测试的方法。
如果所有列值都相同，则必须将行转换为相同的二进制字符串。如果任何列值不同，则必须将行转换为不同的二进制字符串。例如，像下面这样简单的代码将不起作用：
CAST(COL1 AS BINARY(4)) + CAST(COL2 AS BINARY(4)) + ..
它不能正确处理零。如果COL1第 1COL2行为 NULL，第 2 行为 NULL，则这两行都将转换为 NULL 字符串。我相信正确处理 NULL 是正确转换整行最难的部分。INT 列的所有允许值都是可能的。
提出一些问题：
- 如果重要的话，大部分时间（90% 以上）列不会为 NULL。
- 我必须使用CLR。
- 我必须放置这么多行。我不能坚持哈希。
- 我相信由于 CLR 功能的存在，我不能使用批处理模式进行转换。
将 32 个可空INT列转换为BINARY(X)字符串VARBINARY(X)的最快方法是什么？
承诺的示例数据和代码：
-- create sample data
DROP TABLE IF EXISTS dbo.TABLE_OF_32_INTS;
CREATE TABLE dbo.TABLE_OF_32_INTS (
COL1 INT NULL,
COL2 INT NULL,
COL3 INT NULL,
COL4 INT NULL,
COL5 INT NULL,
COL6 INT NULL,
COL7 INT NULL,
COL8 INT NULL,
COL9 INT NULL,
COL10 INT NULL,
COL11 INT NULL,
COL12 INT NULL,
COL13 INT NULL,
COL14 INT NULL,
COL15 INT NULL,
COL16 INT NULL,
COL17 INT NULL,
COL18 INT NULL,
COL19 INT NULL,
COL20 INT NULL,
COL21 INT NULL,
COL22 INT NULL,
COL23 INT NULL,
COL24 INT NULL,
COL25 INT NULL,
COL26 INT NULL,
COL27 INT NULL,
COL28 INT NULL,
COL29 INT NULL,
COL30 INT NULL,
COL31 INT NULL,
COL32 INT NULL
);
INSERT INTO dbo.TABLE_OF_32_INTS WITH (TABLOCK)
SELECT 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, 0, 123, 12345, 1234567, 123456789
, NULL, -876545321
FROM
(
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);
GO
-- procedure to test performance
CREATE OR ALTER PROCEDURE #p AS
BEGIN
SET NOCOUNT ON;
DECLARE
@counter INT = 0,
@dummy VARBINARY(8000);
WHILE @counter < 10
BEGIN
SELECT @dummy = -- this code is clearly incomplete as it does not handle NULLs
CAST(COL1 AS BINARY(4)) +
CAST(COL2 AS BINARY(4)) +
CAST(COL3 AS BINARY(4)) +
CAST(COL4 AS BINARY(4)) +
CAST(COL5 AS BINARY(4)) +
CAST(COL6 AS BINARY(4)) +
CAST(COL7 AS BINARY(4)) +
CAST(COL8 AS BINARY(4)) +
CAST(COL9 AS BINARY(4)) +
CAST(COL10 AS BINARY(4)) +
CAST(COL11 AS BINARY(4)) +
CAST(COL12 AS BINARY(4)) +
CAST(COL13 AS BINARY(4)) +
CAST(COL14 AS BINARY(4)) +
CAST(COL15 AS BINARY(4)) +
CAST(COL16 AS BINARY(4)) +
CAST(COL17 AS BINARY(4)) +
CAST(COL18 AS BINARY(4)) +
CAST(COL19 AS BINARY(4)) +
CAST(COL20 AS BINARY(4)) +
CAST(COL21 AS BINARY(4)) +
CAST(COL22 AS BINARY(4)) +
CAST(COL23 AS BINARY(4)) +
CAST(COL24 AS BINARY(4)) +
CAST(COL25 AS BINARY(4)) +
CAST(COL26 AS BINARY(4)) +
CAST(COL27 AS BINARY(4)) +
CAST(COL28 AS BINARY(4)) +
CAST(COL29 AS BINARY(4)) +
CAST(COL30 AS BINARY(4)) +
CAST(COL31 AS BINARY(4)) +
CAST(COL32 AS BINARY(4))
FROM dbo.TABLE_OF_32_INTS
OPTION (MAXDOP 1);
SET @counter = @counter + 1;
END;
SELECT cpu_time
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
END;
GO
-- run procedure
EXEC #p;
（我仍然会在这个二进制结果上使用诡异的哈希。工作负载使用哈希连接，哈希值用于其中一个哈希构建。我不希望哈希构建中有一个长二进制值，因为它需要太多记忆。）
在我的机器 (SQL Server 2017) 上，以下 C# SQLCLR 函数的运行速度比这个想法快 30%，比
binary(5)35% 快CONCAT_WS，并且在自我回答的一半时间内运行。It requires
UNSAFEpermission and uses pointers. The implementation is very specifically tied to the test data.For testing purposes, the easiest way to get this unsafe assembly working is to set the database to
TRUSTWORTHYand disable the clr strict security configuration option if necessary.Compiled code
For convenience the
CREATE ASSEMBLYcompiled bits are at https://gist.github.com/SQLKiwi/72d01b661c74485900e7ebcfdc63ab8eT-SQL Function Stub
Source code
The C# source is at https://gist.github.com/SQLKiwi/64f320fe7fd802a68a3a644aa8b8af9f
If you compile this for yourself, you must use a Class Library (.dll) as the target project type and check the Allow Unsafe Code build option.
Combined solution
Since you ultimately want to compute the SpookyHash of the binary data returned above, you can call SpookyHash within the CLR function and return the 16-byte hash.
An example implementation based on a table with a mixture of column data types is at https://gist.github.com/SQLKiwi/6f82582a4ad1920c372fac118ec82460. This includes an unsafe inlined version of the Spooky Hash algorithm derived from Jon Hanna's SpookilySharp and the original public domain C source code by Bob Jenkins.
一
INT列有四个字节的允许值，它们与 a 的大小完全匹配BINARY(4)。换句话说，BINARY(4) 的每个可能值都与INT列的可能值相匹配。因此，除非列中存在不允许的值，否则INTNULL 没有安全的替代品。列是否为 NULL 必须单独编码。它根本无法放入BINARY(4).一种方法是使用 NULL 位图。考虑以下代码：
八列是否为 NULL 适合单个字节。这些表达式可以在行之间进行比较，以检查所有相同的列是否为 NULL 或非 NULL。有了这些附加信息，就可以安全地将 NULL 列值替换为任何非 NULL 值。我发现
CAST(ISNULL(COL1, 0) AS BINARY(4))它是最快的，尽管其他变化ISNULL(CAST(COL1 AS VARBINARY(4)), 0x)是可能的。很难肯定地证明任何事情，但我发现以下细节是最快的：
在我的机器上，基准测试需要大约 27.5 CPU 秒。不幸的是，NULL 位图步骤大约需要三分之一的时间。如果有更快的方法来做到这一点，那就太好了。
这是完整的解决方案：
使用
BINARY(5)NULL 并将其转换为 INT 范围之外的内容怎么样：在我的测试中， concat_ws比您的空位图解决方案（26 秒）快一点（18 秒）。将有更多数据需要随机播放，因此您可能会在其他地方看到一些性能下降，如果您想将其与字符列混合，您必须明智地选择分隔符。
如果您可以提前确保不存储某些特定的 int，
-2,147,483,648那么您可以执行以下操作：