SQL checksum keyword

Source: Internet
Author: User
Tags comparable md5 hash

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


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



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.


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

Return type



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.


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.