BINARY_CHECKSUM AND CHECKSUM

In all the years I've been working with SQL Server I have never seen anyone use these functions.

It's purpose is to build hash indices speeding up queries by matching a 4-byte int instead of a costly character comparison. Especially on large text columns the function can be of use.

For more information check out the BOL.

I'll take the example from BOL (c) Microsoft

-- Create a checksum index.
SET ARITHABORT ON
USE
Northwind
GO
ALTER TABLE Products
ADD cs_Pname AS
checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)

/*Use the index in a
SELECT query. Add a second search
condition to
catch stray cases where
checksums match,
but the values are not
identical.*/
SELECT *
FROM Products
WHERE checksum(N'Vegie-spread') =
cs_Pname
AND
ProductName = N'Vegie-spread'

There's also a little brother called CHECKSUM_AGG which returns the checksum of the values in a group. The example in BOL uses it to track changes in data.