CHECKSUM() 和 BINARY_CHECKSUM() 之间有什么区别以及何时/什么是合适的使用场景? [英] What are the differences between CHECKSUM() and BINARY_CHECKSUM() and when/what are the appropriate usage scenarios?
问题描述
同样，MSDN 并没有真正用通俗的英语解释确切的区别，或何时选择另一个的信息.
Again MSDN does not really explain in plain English the exact difference, or the information for when to choose one over the other.
返回对表的一行或表达式列表计算的校验和值.CHECKSUM 旨在用于构建哈希索引.
Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes.
返回对表的一行或表达式列表计算的二进制校验和值.BINARY_CHECKSUM 可用于检测表中一行的更改.
Returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.
它确实暗示应该使用二进制校验和来检测行更改，但没有暗示为什么.
It does hint that binary checksum should be used to detect row changes, but not why.
推荐答案
查看以下突出差异的博客文章.
Check out the following blog post that highlights the diferences.
http://decipherinfosys.wordpress.com/2007/05/18/checksum-functions-in-sql-server-2005/
通过此链接添加信息:
CHECKSUM 函数的主要目的是基于表达式或列列表构建哈希索引.如果说您使用它来计算和存储表级别的列以表示使记录在表中唯一的列的校验和，那么这有助于确定行是否已更改.然后可以使用此机制而不是连接使记录唯一的所有列，以查看记录是否已更新.SQL Server 联机丛书有很多关于此功能的示例.
The key intent of the CHECKSUM functions is to build a hash index based on an expression or a column list. If say you use it to compute and store a column at the table level to denote the checksum over the columns that make a record unique in a table, then this can be helpful in determining whether a row has changed or not. This mechanism can then be used instead of joining with all the columns that make the record unique to see whether the record has been updated or not. SQL Server Books Online has a lot of examples on this piece of functionality.
使用这些函数时需要注意的几点:
A couple of things to watch out for when using these functions:
您需要确保要比较的两个校验和之间的列或表达式顺序相同，否则值会不同并导致问题.
You need to make sure that the column(s) or expression order is the same between the two checksums that are being compared else the value would be different and will lead to issues.
我们不建议使用校验和(*)，因为以这种方式生成的值将基于运行时表定义的列顺序，这很容易在一段时间内发生变化.因此，明确定义列列表.
We would not recommend using checksum(*) since the value that will get generated that way will be based on the column order of the table definition at run time which can easily change over a period of time. So, explicitly define the column listing.
当您包含 datetime 数据类型列时要小心，因为粒度是 1/300 秒，即使是很小的变化也会导致不同的校验和值.因此，如果您必须使用日期时间数据类型列，请确保获得确切的日期 + 小时/分钟.即您想要的粒度级别.
Be careful when you include the datetime data-type columns since the granularity is 1/300th of a second and even a small variation will result into a different checksum value. So, if you have to use a datetime data-type column, then make sure that you get the exact date + hour/min. i.e. the level of granularity that you want.
您可以使用三种校验和函数:
There are three checksum functions available to you:
CHECKSUM:上面已经描述过了.
CHECKSUM: This was described above.
CHECKSUM_AGG:返回组中值的校验和，在这种情况下忽略空值.这也适用于 SQL Server 2005 中新的分析函数的 OVER 子句.
CHECKSUM_AGG: This returns the checksum of the values in a group and Null values are ignored in this case. This also works with the new analytic function’s OVER clause in SQL Server 2005.
BINARY_CHECKSUM:顾名思义，它返回在一行或表达式列表上计算的二进制校验和值.CHECKSUM 和 BINARY_CHECKSUM 之间的区别在于为字符串数据类型生成的值.这种差异的一个例子是为DECIPHER"和解密"生成的值在 BINARY_CHECKSUM 的情况下将不同，但对于 CHECKSUM 函数将相同(假设我们安装了不区分大小写的实例).另一个区别在于表达式的比较.如果两个表达式的元素具有相同的类型和字节表示，则 BINARY_CHECKSUM() 返回相同的值.因此，2Volvo Director 20"和3Volvo Director 30"将产生相同的值，但是 CHECKSUM() 函数会评估类型并比较两个字符串，如果它们相等，则仅返回相同的值.
BINARY_CHECKSUM: As the name states, this returns the binary checksum value computed over a row or a list of expressions. The difference between CHECKSUM and BINARY_CHECKSUM is in the value generated for the string data-types. An example of such a difference is the values generated for "DECIPHER" and "decipher" will be different in the case of a BINARY_CHECKSUM but will be the same for the CHECKSUM function (assuming that we have a case insensitive installation of the instance). Another difference is in the comparison of expressions. BINARY_CHECKSUM() returns the same value if the elements of two expressions have the same type and byte representation. So, "2Volvo Director 20" and "3Volvo Director 30" will yield the same value, however the CHECKSUM() function evaluates the type as well as compares the two strings and if they are equal, then only the same value is returned.
Example:
STRING BINARY_CHECKSUM_USAGE CHECKSUM_USAGE
------------------- ---------------------- -----------
2Volvo Director 20 -1356512636 -341465450
3Volvo Director 30 -1356512636 -341453853
4Volvo Director 40 -1356512636 -341455363
这篇关于CHECKSUM() 和 BINARY_CHECKSUM() 之间有什么区别以及何时/什么是合适的使用场景?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
