CHECKSUM function is intended to use for creating hash indices. It returns the checksum value computed for row of a table or list of expressions.

CHECKSUM function returns an error if any column is of noncomparable  data types in its computation. Noncomparable data types are text, ntext, image, and cursor, sql_variant with any of the above types as its base type.

The CHECKSUM function generaly used to build hash indices. The hash index is built by adding a computed checksum column to the table being indexed, then building an index on the checksum column.

The checksum index can be used as a hash index, to improve indexing speed when the column to be indexed is a long character column. The checksum index can be used for equality searches.

Syntax of CHECKSUM function :

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

*        means that the computation is over all the columns of the table.

Expression is any valid sql expression of any type.

Example of CHECKSUM function :

Example 1 : Use of CHECKSUM function in SELECT clause

SELECT ProductID, CHECKSUM(ProductName) AS 'Checksum'

FROM   Products 

Output

ProductID           Checksum

      1                    1384010367

      2                     529833873

      3                     911420344

      4                   -1712497368

      5                    1499143079

Above example displays checksum of productname field in the products table.

Example 2 : Using CHECKSUM function to create index of a table and in WHERE clause

ALTER TABLE Products

ADD INX_Pname AS CHECKSUM(ProductName)

CREATE INDEX Pname_index ON Products (INX_Pname)

SELECT ProductName, INX_Pname 

FROM Products

WHERE CHECKSUM(N'Chai') = INX_Pname AND ProductName = N'Chai'

Output

ProductName    INX_Pname

Chai              1384010367

Any changes to the ProductName value will be propagated to the checksum column.