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.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
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 specified 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 specified 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 for BINARY_CHECKSUM(*) is the order of columns specified in the table or view definition. These include 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 with BINARY_CHECKSUM values.
The following example uses BINARY_CHECKSUM to detect changes in a row of a table.
USE AdventureWorks2012; GO CREATE TABLE myTable (column1 int, column2 varchar(256)); GO INSERT INTO myTable VALUES (1, 'test'); GO SELECT BINARY_CHECKSUM(*) from myTable; GO UPDATE myTable set column2 = 'TEST'; GO SELECT BINARY_CHECKSUM(*) from myTable; GO