?
Solved

Explanation of SQL HASHBYTES command

Posted on 2011-09-26
17
Medium Priority
?
876 Views
Last Modified: 2012-05-12
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
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 5

Accepted Solution

by:
eridanix earned 1748 total points
ID: 36598231
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
 
LVL 6

Assisted Solution

by:dan_mason
dan_mason earned 252 total points
ID: 36598260
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
 

Author Comment

by:rayluvs
ID: 36598747
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:rayluvs
ID: 36598753
Also, can you briefly explain the MD2, MD4, MD5, SHA, or SHA1 formats?
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36598801
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
 

Author Comment

by:rayluvs
ID: 36710676
Thanx... what would be the equivalent for SQL 2000 for CHECKSUM and HASBYTES ?
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36710991
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
 

Author Comment

by:rayluvs
ID: 36718447
Thanx, but what would be the equivalent for SQL 2000 for CHECKSUM and HASBYTES, if any?
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36813204
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
 

Author Comment

by:rayluvs
ID: 36814404
I was told that these 2 statement are not for 2000 and I was asking for the equivalent statement.  
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36814570
But there is CHECKSUM form SQL Server 2000.
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx
0
 

Author Comment

by:rayluvs
ID: 36815300
yes thanks... what about for HASBYTES, is there an equivalent statement for sql 2000?
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36815528
I'm no sure, but I mean, there is now equivalent for HASBYTES in sql 2000.
0
 

Author Comment

by:rayluvs
ID: 36818339
sorry don't understand, you there is NOW an equivalent or there is NO equivalent.
0
 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 1748 total points
ID: 36890172
sorry for typing error, NO is correct
0
 

Author Comment

by:rayluvs
ID: 36891471
Thanx
0
 

Author Closing Comment

by:rayluvs
ID: 36891479
Thanx
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question