You can find a number of posts on this subject by doing a web search on "SQL Server Checksum false positives". Something related but not discussed as much are the rows that are different yet generate the same checksum value. SQL Server 2005 can generated checksums that are exactly the same in spite of the fact that the data is different. To try it for yourself:

CREATE TABLE DupeTest

(FieldA INT,

FieldB INT,

RowChecksum AS CHECKSUM(FieldA, FieldB))

INSERT DupeTest (FieldA, FieldB)

VALUES (384, 170005);

INSERT DupeTest (FieldA, FieldB)

VALUES (387, 170021);

SELECT * FROM dupetest

DROP TABLE DupeTest