CHECKSUM (Transact-SQL)CHECKSUM (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

CHECKSUM 函数返回按照表的某一行或一组表达式计算出来的校验和值。The CHECKSUM function returns the checksum value computed over a table row, or over an expression list. 使用 CHECKSUM 来生成哈希索引。Use CHECKSUM to build hash indexes.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

CHECKSUM ( * | expression [ ,...n ] )  

参数Arguments

*
此参数指定涵盖了所有表列的校验和计算。This argument specifies that the checksum computation covers all table columns. 如果有任一列具有非可比数据类型,则 CHECKSUM 返回错误。CHECKSUM returns an error if any column has a noncomparable data type. 非可比数据类型包括:Noncomparable data types include:

  • cursorcursor
  • 图像image
  • ntextntext
  • texttext
  • XMLXML

另一个非可比数据类型为,以上述任一数据类型作为基类型的 sql_variant 。Another noncomparable data type is sql_variant with any one of the preceding data types as its base type.

expressionexpression
除非可比数据类型之外的任何类型的表达式An expression of any type, except a noncomparable data type.

返回类型Return types

intint

RemarksRemarks

CHECKSUM 对其参数列表计算一个称为校验和的哈希值。CHECKSUM computes a hash value, called the checksum, over its argument list. 使用哈希值生成哈希索引。Use this hash value to build hash indexes. 如果 CHECKSUM 函数具有列参数,则结果是一个哈希索引,并且对计算的 CHECKSUM 值生成索引。A hash index will result if the CHECKSUM function has column arguments, and an index is built over the computed CHECKSUM value. 它可用于对列进行等价搜索。This can be used for equality searches over the columns.

CHECKSUM 函数满足哈希函数的属性:CHECKSUM 在使用等于 (=) 运算符比较时,如果两个列表的相应元素具有相同数据类型且对应的元素相等,则在任何两个表达式列表上应用的 BINARY_CHECKSUM 将返回同一值。The CHECKSUM function satisfies hash function properties: CHECKSUM applied over any two lists of expressions will return the same value, if the corresponding elements of the two lists have the same data type, and if those corresponding elements have equality when compared using the equals (=) operator. 因为 CHECKSUM 函数,指定类型的 Null 值被定义为相等进行比较。Null values of a specified type are defined to compare as equal for CHECKSUM function purposes. 如果表达式列表中的至少一个值发生更改,则列的校验和很可能也会更改。If at least one of the values in the expression list changes, the list checksum will probably change. 但是,这一点无法保证。However, this is not guaranteed. 因此,若要检测值是否更改,建议仅当应用程序可以容忍偶然错过更改时,使用 CHECKSUMTherefore, to detect whether values have changed, we recommend use of CHECKSUM only if your application can tolerate an occasional missed change. 否则,请考虑改用 HASHBYTESOtherwise, consider using HASHBYTES instead. 使用指定的 MD5 哈希算法时,HASHBYTES 为两个不同输入返回相同结果的可能性要比 CHECKSUM 小得多。With a specified MD5 hash algorithm, the probability that HASHBYTES will return the same result, for two different inputs, is much lower compared to CHECKSUM.

表达式顺序会影响计算 CHECKSUM 值。The expression order affects the computed CHECKSUM value. 用于 CHECKSUM(*) 的列顺序是在表或视图定义中指定的列顺序。The order of columns used for CHECKSUM(*) is the order of columns specified in the table or view definition. 其中包括计算列。This includes computed columns.

CHECKSUM 值取决于排序规则。The CHECKSUM value depends on the collation. 使用不同排序规则存储的相同值将返回一个不同的 CHECKSUM 值。The same value stored with a different collation will return a different CHECKSUM value.

CHECKSUM () 不保证结果唯一。CHECKSUM () does not guarantee unique results.

示例Examples

这些示例显示了如何使用 CHECKSUM 来生成哈希索引。These examples show the use of CHECKSUM to build hash indexes.

为生成哈希索引,第一个示例将已计算的校验和列添加到我们想要索引的表。To build the hash index, the first example adds a computed checksum column to the table we want to index. 然后在校验和列生成索引。It then builds an index on the checksum column.

-- 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  

此示例显示将校验和索引用作哈希索引。This example shows the use of a checksum index as a hash index. 当索引的列为长字符列时,这可加快索引的速度。This can help improve indexing speed when the column to index is a long character column. 校验和索引可用于等价搜索。The checksum index can be used for equality searches.

/*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 值所做的任何更改都将传播到校验和列。Index creation on the computed column materializes the checksum column, and any changes to the ProductName value will propagate to the checksum column. 或者,我们可以在想要索引的列上直接生成索引。Alternatively, we could build an index directly on the column we want to index. 但是,对于长密钥值,常规索引的执行很可能不如校验和索引。However, for long key values, a regular index will probably not perform as well as a checksum index.

另请参阅See also

CHECKSUM_AGG (Transact-SQL)CHECKSUM_AGG (Transact-SQL)
HASHBYTES (Transact-SQL)HASHBYTES (Transact-SQL)
BINARY_CHECKSUM (Transact-SQL)BINARY_CHECKSUM (Transact-SQL)