Checksum comparing two table field value differences

CHECKSUM

Returns a checksum value that is computed on the row of a table or on an expression list. The CHECKSUM is used to generate a hash index.

Grammar

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

Parameters

*

Specifies that calculations are made on all columns of the table. If either column is a non-comparable data type, CHECKSUM returns an error. The non-comparable data type is the text,ntext,image,cursor , and sql_variantof one of the first 4 data types in the base type.

Expression

Is any type of expression other than the one that is comparable to the data type.

return type

Int

Comments

CHECKSUM calculates a hash value, called a checksum, on its argument list. This hash value is used to generate a hash index. If the CHECKSUM parameter is a column and the index is generated on the computed CHECKSUM value, the result is a hash index that can be used for equivalent searches on the column.

CHECKSUM satisfies the following properties of the hash function: When using the equals (=) operator comparison, if the corresponding elements of the two list have the same type and are equal, the CHECKSUM applied on any two expression lists will return the same value. The purpose of this definition is to: a NULL value of a given type is compared as "equal". If a value in the expression list changes, the checksum value of the list usually changes. However, in rare cases, the checksum will remain unchanged.

Binary_checksum and CHECKSUM have similar functionality: they can be used to calculate checksum values on an expression list, and the order of the expressions affects the resulting values. The column order used in CHECKSUM (*) is the column order specified in the table or view definition, including computed columns.

CHECKSUM and binary_checksum only return different values for string data types, which can cause strings with different representations to be compared in an equivalent way. The string data type is char,varchar,nchar,nvarchar , or sql_variant(if its base type is a string data type). For example, the Binary_checksum values for the string "McCavity" and "mccavity" are different. Conversely, in a case-insensitive server, the CHECKSUM of the above string returns the same checksum value. The CHECKSUM value should not be compared to the binary_checksum value.

Checksum comparing two table field value differences