CHECKSUM/BINARY_CHECKSUM 和 HASHBYTES 的选择

http://ultrasql.blog.51cto.com/9591438/1607411 

1. 验证已复制的数据

 

复制验证使用 checksum 和 binary_checksum 函数。

 

通过事务复制和合并复制，您可以验证订阅服务器中的数据与发布服务器中的数据是否匹配。 可以对特定订阅或某一发布的所有订阅执行验证。 指定下列验证类型之一，分发代理或合并代理便会在下次运行时验证数据：

 

    仅行计数。 此选项将验证订阅服务器上的表与发布服务器上的表的行数是否相同，但不验证行内容是否匹配。 行计数验证提供了一种轻型验证方法，使您可以意识到数据的问题所在。

 

    行计数和二进制校验和。 除了可在发布服务器和订阅服务器上对行进行计数之外，还可使用校验和算法来计算所有数据的校验和。 如果行计数失败，则不计算校验和。

 

2. 实现哈希分区

 

用CHECKSUM可以实现哈希分区（Hash Partition）。

SQL Server 只有列表分区和范围分区，通过CHECKSUM函数可以创建相应的Hash Partition。

 

步骤1：建议分区函数。

 

1
2
CREATE PARTITION FUNCTION fun_hash (int) AS
RANGE LEFT FOR VALUES (-1073741824, 0, 1073741824);

 

注意：我们这里使用的int型数据，因为hash函数是checksum（id），这里根据int的最大长度进行分区，我们使用2,147,483,648/2=1073741824

这里：Range left 意思： <= Range right 意思： <

 

步骤2：创建分区方案及相应的表。

 

1
2
3
4
5
6
7
CREATE PARTITION SCHEME sche_fun_hash AS PARTITION fun_hash all TO ([PRIMARY])
CREATE TABLE [dbo].test(
[id] [varchar](32) NOT NULL,
[sid] int,
[hashid] AS (checksum([id])) PERSISTED
)
ON sche_fun_hash ([hashid])

 

步骤3：插入数据并查看数据分布情况。

 

1
2
3
4
insert into test(id,sid)
select replace(newid(),'-',''),1
from (select top 100 * from syscolumns) a
,(select top 100 * from syscolumns) b

 

步骤4：查看结果。

 

1
2
3
4
5
SELECT
$partition.fun_hash(hashid)
AS 分区号,count(*) 数据条数
FROM test
group by $partition.fun_hash(hashid)

 

clip_image002

 

3. 生成哈希列

 

CHECKSUM 对其参数列表计算一个称为校验和的哈希值。 此哈希值用于生成哈希索引。

以下示例演示如何使用 CHECKSUM 生成哈希索引。 通过将计算校验和列添加到索引的表中，然后对校验和列生成索引来生成哈希索引。

 

1
2
3
4
5
6
7
8
9
-- Create a checksum index.
SET ARITHABORT ON;
USE AdventureWorks2012;
GO
ALTER TABLE Production.Product
ADD cs_Pname AS CHECKSUM(Name);
GO
CREATE INDEX Pname_index ON Production.Product (cs_Pname);
GO

 

校验和索引可用作哈希索引，尤其是当要索引的列为较长的字符列时可以提高索引速度。 校验和索引可用于等价搜索。

 

1
2
3
4
5
6
7
8
/*Use the index in a SELECT query. Add a second search
condition to catch stray cases where checksums match,
but the values are not the same.*/
SELECT *
FROM Production.Product
WHERE CHECKSUM(N'Bearing Ball') = cs_Pname
AND Name = N'Bearing Ball';
GO

 

对计算列创建索引将具体化为校验和列，对 ProductName 值所做的任何更改都将传播到校验和列。 也可以直接对索引的列生成索引。 然而，如果键值较长，则很可能不执行校验和索引甚至常规索引。

CHECKSUM/BINARY_CHECKSUM 和 HASHBYTES 的选择

 

CHECKSUM 满足哈希函数的下列属性：在使用等于 (=) 运算符比较时，如果两个列表的相应元素具有相同类型且相等，则在任何两个表达式列表上应用的 CHECKSUM 将返回同一值。 对于该定义，指定类型的 Null 值被作为相等进行比较。 如果表达式列表中的某个值发生更改，则列表的校验和通常也会更改。 但只在极少数情况下，校验和会保持不变。 因此，我们不推荐使用 CHECKSUM 来检测值是否更改，除非应用程序可以容忍偶尔丢失更改。 请考虑改用 HashBytes。 指定 MD5 哈希算法时，HashBytes 为两个不同输入返回相同结果的可能性比 CHECKSUM 小得多。

 

在SQL中创建一个短的CHECKSUM或HASH代码，可以选择用CHECKSUM、BINARY_CHECKSUM或HASHBYTES。

 

HASHBYTES是确保低冲突的最佳选择，而BINARY_CHECKSUM是最糟糕的。可以通过下面的语句来比较下性能：

 

1
2
3
4
DECLARE @timeStart AS DATETIME;
SET @timeStart = GETDATE();
SELECT MAX(HASHBYTES('MD5',x)) FROM y;
SELECT DATEDIFF(ms,@timestart,GETDATE());

 

测试发现，BINARY_CHECKSUM的速度和CHECKSUM一样，明显快于MD5加密的HASHBYTES。

 

所以，建议如果相对准确性你更注重速度，使用CHESKSUM，而不是BINARY_CHECKSUM。如果相对速度你更注重准确性，使用HASHBYTES。

 

posted @ 2017-02-16 13:12  俊凯  阅读(367)  评论(0)    收藏  举报