I would like to create unique string columns (32 characters in length) from combination of columns with different data types in SQL Server 2005.
Add a comment
|
2 Answers
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
1 Comment
Marius
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. learn.microsoft.com/en-us/sql/t-sql/functions/…
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 4x1038 hashed elements (see birthday problem), that assumes a perfect distribution for your 32 bytes output hash function, which you're not going to achieve.
