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.
Syntax
BINARY_CHECKSUM ( * | expression [ ,...n ] )
Arguments
*
Specifies that the computation is over all the columns of the table. BINARY_CHECKSUM ignores columns of noncomparable data types in its computation. Noncomparable data types are text, ntext, image, and cursor, as well as sql_variant with any of the above types as its base type.
expression
Is an expression of any type. BINARY_CHECKSUM ignores expressions of noncomparable data types in its computation.
Remarks
BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.
BINARY_CHECKSUM can be applied over a list of expressions, and returns the same value for a given list. BINARY_CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and byte representation. For this definition, NULL values of a given type are considered to have the same byte representation.
BINARY_CHECKSUM and CHECKSUM are similar functions: they can be used to compute a checksum value on a list of expressions, and the order of expressions affects the resultant value. The order of columns used in the case of BINARY_CHECKSUM(*) is the order of columns specified in the table or view definition, including computed columns.
CHECKSUM and BINARY_CHECKSUM return different values for the string data types, where locale can cause strings with different representation to compare equal. The string data types are char, varchar, nchar, nvarchar, or sql_variant (if the base type of sql_variant is a string data type). For example, the BINARY_CHECKSUM values for the strings "McCavity" and "Mccavity" are different. In contrast, in a case-insensitive server, CHECKSUM returns the same checksum values for those strings. CHECKSUM values should not be compared against BINARY_CHECKSUM values.
Examples
A. Use BINARY_CHECKSUM to detect changes in the rows of a table.
This example uses BINARY_CHECKSUM to detect changes in a row of the Products table in the Northwind database.
/*Get the checksum value before the values in the specific rows (#13-15) are changed.*/
USE Northwind
GO
CREATE TABLE TableBC (ProductID int, bchecksum int)
INSERT INTO TableBC
SELECT ProductID, BINARY_CHECKSUM(*)
FROM Products
/*TableBC contains a column of 77 checksum values corresponding to each row in the Products table.*/
--A large company bought products 13-15.
--The new company modified the products names and unit prices.
--Change the values of ProductsName and UnitPrice for rows 13, 14, and 15 of the Products table.*/
UPDATE Products
SET ProductName='Oishi Konbu', UnitPrice=5
WHERE ProductName='Konbu'
UPDATE Products
SET ProductName='Oishi Tofu', UnitPrice=20
WHERE ProductName='Tofu'
UPDATE Products
SET ProductName='Oishi Genen Shouyu', UnitPrice=12
WHERE ProductName='Genen Shouyu'
--Determine the rows that have changed.
SELECT ProductID
FROM TableBC
WHERE EXISTS (
SELECT ProductID
FROM Products
WHERE Products.ProductID = TableBC.ProductID
AND BINARY_CHECKSUM(*) <> TableBC.bchecksum)
Here is the result set:
ProductID
13
14
15