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.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.
SET ARITHABORT ON
ALTER TABLE Products
ADD cs_Pname AS
CREATE INDEX Pname_index ON Products (cs_Pname)
/*Use the index in a
SELECT query. Add a second search
catch stray cases where
but the values are not
WHERE checksum(N'Vegie-spread') =
ProductName = N'Vegie-spread'