• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • Last Modified:

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
0
rayluvs
Asked:
rayluvs
  • 9
  • 7
8 Solutions
 
eridanixCommented:
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_masonCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rayluvsAuthor Commented:
Also, can you briefly explain the MD2, MD4, MD5, SHA, or SHA1 formats?
0
 
eridanixCommented:
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
 
eridanixCommented:
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
 
eridanixCommented:
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
 
eridanixCommented:
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
 
eridanixCommented:
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
 
eridanixCommented:
sorry for typing error, NO is correct
0
 
rayluvsAuthor Commented:
Thanx
0
 
rayluvsAuthor Commented:
Thanx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now