Solved

Explanation of SQL HASHBYTES command

Posted on 2011-09-26
17
831 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 437 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 63 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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 437 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 437 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 437 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 437 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 437 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 437 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 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