Solved

Explanation of SQL HASHBYTES command

Posted on 2011-09-26
17
786 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
  • 9
  • 7
17 Comments
 
LVL 5

Accepted Solution

by:
eridanix earned 437 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rayluvs
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanx, but what would be the equivalent for SQL 2000 for CHECKSUM and HASBYTES, if any?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 5

Assisted Solution

by:eridanix
eridanix earned 437 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm no sure, but I mean, there is now equivalent for HASBYTES in sql 2000.
0
 

Author Comment

by:rayluvs
Comment Utility
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
Comment Utility
sorry for typing error, NO is correct
0
 

Author Comment

by:rayluvs
Comment Utility
Thanx
0
 

Author Closing Comment

by:rayluvs
Comment Utility
Thanx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now