Creating unique hash code (string) in SQL Server from a combination of two or more columns (of different data types)

Question

I would like to create unique string columns (32 characters in length) from combination of columns with different data types in SQL Server 2005.

score 8 · Accepted Answer · edited May 23 '17 at 11:55

8

I have found out the solution elsewhere in StackOverflow

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'HelloWorld')), 3, 32)

The answer thread is here

edited May 23 '17 at 11:55

Community

1
1

answered Apr 25 '11 at 12:37

Nagesh

1,288
3
22
46

Please note that beginning with SQL Server 2016, all algorithms other than SHA2_256 and SHA2_512 are deprecated.Older algorithms (not recommended) will continue working, but they will raise a deprecation event. https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql – Marius May 23 '17 at 08:00

score 2 · Answer 2 · answered Mar 01 '11 at 05:36

With HASBYTES you can create SHA1 hashes, that have 20 bytes, and you can create MD5 hashes, 16 bytes. There are various combination algorithms that can produce arbitrary length material by repeated hash operations, like the PRF of TLS (see RFC 2246).

This should be enough to get you started. You need to define what '32 characters' mean, since hash functions produce bytes not characters. Also, you need to internalize that no algorithm can possibly produce hashes of fixed length w/o collisions (guaranteed 'unique'). Although at 32 bytes length (assuming that by 'characters' you mean bytes) the theoretical collision probability of 50% is at 4x10³⁸ hashed elements (see birthday problem), that assumes a perfect distribution for your 32 bytes output hash function, which you're not going to achieve.

Creating unique hash code (string) in SQL Server from a combination of two or more columns (of different data types)

2 Answers2