Explanation of SQL HASHBYTES command

We are changing master ID codes for security purposes and have been recommended to use CHECKSUM and HASHBYTES.  CHECKSUM faild in some records and want to try HASHBYTES.

We understand CHECKSUM, but haven't quite grasp what is HASHBYTES and its format in use.  Please give us an explanation.

The Master ID codes of the different tables ranges from length of 19 to 15.

Note: we running SQL 2000/2005
rayluvsAsked:
Who is Participating?
 
eridanixConnect With a Mentor Commented:
Explanation of HASBYTES is on this page:
http://www.bidn.com/blogs/indupriya/bidn-blog/554/checksum-vs-hashbytes

Exactly this part:
What is hashbytes function?

HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats.

When can you use a hash function?

Hash functions are a useful option to improve the efficiency of particular queries on large volumes of data. For example, on long strings of text, you can build a hash-index to perform efficient lookups or to speed up aggregate operations.


On the end of page, there is also comparation between CHECKSUM and HASHBYTES.

Good explanations are also here:
http://mytechlearning.blogspot.com/2010/03/checksum-vs-hashbytes.html
http://www.simple-talk.com/community/blogs/johnm/archive/2008/12/01/70713.aspx



0
 
dan_masonConnect With a Mentor Commented:
HASHBYTES is a quick way of creating the hash equivalent of a string.

Let's say, for example you want the MD5 hash of the text string "carrots". You would derive it like this:

SELECT HASHBYTES('MD5','carrots')

Instead of MD5 you can use MD2, MD4, SHA or SHA1

That works fine inside the database, but there is a slight issue with the above if you want the output to 'match' what you would usually consider to be an MD5 string: HASHBYTES returns a varbinary value. To output as a varchar, you'd need to use fn_varbintohexstr and SUBSTRING, as per the code sample below. Of course you could turn this into a function too.

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

Open in new window

0
 
rayluvsAuthor Commented:
We learned to use CHECKSUM (assisted by EE) to replace Master Account ID (a security process we are doing so programmers can work with real structure database but with somewhat encrypted data), but we thought that we can do this for the SQL 2000 (the link says that its only for SQL 2005).

      What would be the equivalent for SQL 2000?

Also recommended by EE was to use HASHBYTES for when CHECKSUM can cause a duplicate value; hence caused duplicity.

      What would be the equivalent for SQL 2000?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
rayluvsAuthor Commented:
Also, can you briefly explain the MD2, MD4, MD5, SHA, or SHA1 formats?
0
 
eridanixConnect With a Mentor Commented:
MD2, MD4, MD5, SHA, or SHA1 are hash algorithms.
You can find a lot of atricles and documents about then on internet, for example here ftp://ftp.rsa.com/pub/pdfs/bulletn4.pdf

If there is not HASBYTES function in SQL 2000, you can use some function, which support on of MD2, MD4, MD5, SHA, or SHA1 algorithms.

0
 
rayluvsAuthor Commented:
Thanx... what would be the equivalent for SQL 2000 for CHECKSUM and HASBYTES ?
0
 
eridanixConnect With a Mentor Commented:
I mean, yes, but be carefull, CHECKSUM can produce lots of duplicate values.
Else only importent different is, that CHECKSUM function return int values.
0
 
rayluvsAuthor Commented:
Thanx, but what would be the equivalent for SQL 2000 for CHECKSUM and HASBYTES, if any?
0
 
eridanixConnect With a Mentor Commented:
I think, that using CHECKSUM in SQL 2000 is the best and fastest, than anything else.
In fact, number of functions in SQL 2000 is limited against newer versions, so, I mean there is no other rationally useful equivalent for CHECKSUM or HASBYTES.
0
 
rayluvsAuthor Commented:
I was told that these 2 statement are not for 2000 and I was asking for the equivalent statement.  
0
 
eridanixConnect With a Mentor Commented:
But there is CHECKSUM form SQL Server 2000.
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx
0
 
rayluvsAuthor Commented:
yes thanks... what about for HASBYTES, is there an equivalent statement for sql 2000?
0
 
eridanixConnect With a Mentor Commented:
I'm no sure, but I mean, there is now equivalent for HASBYTES in sql 2000.
0
 
rayluvsAuthor Commented:
sorry don't understand, you there is NOW an equivalent or there is NO equivalent.
0
 
eridanixConnect With a Mentor Commented:
sorry for typing error, NO is correct
0
 
rayluvsAuthor Commented:
Thanx
0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.