If you use HASHBYTES with the SHA1 you can expect a collision after you generate hashes for about 1,208,925,819,614,629,174,706,176 rows of data. This would take me a few million years. If you use the MD5, collisions are much more common.

Here is a really good article about it:

http://sqlblog.com/blogs/michael_coles/archive/2010/04/17/find-a-hash-collision-win-100.aspx