SQL checksum keyword

Checksum (TRANSACT-SQL)

Returns the verification value calculated based on a row or a group of expressions in the table. Checksum is used to generate a hash index.

Transact-SQL syntax conventions

Syntax

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

Parameters

*

All columns of the table are calculated. If any column is of a non-comparable data type, checksum returns an error. Non-comparable data types includeText,Ntext,Image, XML andCursor, Including any of the above types as the base typeSQL _variant.

Expression

Unless it is an expression of any type other than the comparable data type.

Return type

Int

Note

Checksum calculates a hash value called a checksum for its parameter list. This hash value is used to generate a hash index. If the checksum parameter is a column and an index is generated for the calculated checksum value, the result is a hash index. It can be used to perform equivalent searches on columns.

Checksum satisfies the following attributes of the hash function: When the equals (=) operator is used for comparison, if the corresponding elements of the two lists have the same type and are equal, the Checksum applied to the list of any two expressions returns the same value. For this definition, the null value of the specified type is compared as equal. If a value in the expression list is changed, the checksum of the list is also changed. However, in rare cases, the checksum will remain unchanged. Therefore, we do not recommend using checksum to check whether the value is changed unless the applicationProgramOccasional changes can be tolerated. Use hashbytes. MD5 HashAlgorithmHashbytes is much less likely to return the same result for two different inputs than checksum.

The order of expressions affects the result value of checksum. The column sequence used for checksum (*) is the column sequence specified in the table or view definition. This includes calculation columns.

Example

The following example shows how to useChecksumGenerate a hash index. Add the calculated checksum column to the index table, and then generate an index for the checksum column to generate a hash index.

Copy code

-- Create a checksum index. Set arithabort on; Use adventureworks; goalter table production. productadd cs_pname as checksum (name); gocreate index pname_index on production. Product (cs_pname); go
 

Checksum indexes can be used as hash indexes, especially when the columns to be indexed are long character columns, the indexing speed can be improved. Checksum indexes can be used for equivalent searches.

Copy code

 
/* 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. productwhere checksum (n'bearing ball ') = cs_pnameand name = n' bearing ball'; go
 

Creating an index for a calculated column will be embodied as a checksum column.ProductnameAny changes made to the value will be propagated to the checksum column. You can also directly generate an index for the index column. However, if the key value is long, it is likely that the checksum and index are not executed, or even the regular index.

From: http://msdn.microsoft.com/zh-cn/library/ms189788.aspx