Home Library Learn Downloads Support Community
Sign in | United States - English | |
8 out of 18 rated this helpful - Rate this topic

HashBytes (Transact-SQL)

SQL Server 2005

Returns the MD2, MD4, MD5, SHA, or SHA1 hash of its input.

Topic link icon Transact-SQL Syntax Conventions

Copy 

HashBytes ( '<algorithm>', { @input | 'input' } )

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1
'<algorithm>'

Identifies the hashing algorithm to be used to hash the input. This is a required argument with no default. The single quotation marks are required.

@input

Specifies a variable containing the data to be hashed. @input is varchar, nvarchar, or varbinary.

' input '

Specifies a string to be hashed.

varbinary (maximum 8000 bytes)

The following example returns the SHA1 hash of the nvarchar data stored in variable @HashThis.

Copy 
DECLARE @HashThis nvarchar(max);
SELECT @HashThis = CONVERT(nvarchar,'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
What is the difference between MD2, MD4, MD5, SHA, and SHA1?
This documentation is incomplete. You need to describe, in a chart or table, exactly what the differences are between MD2, MD4, MD5, SHA, and SHA1. The link "Choosing an Encryption Algorithm" also fails to describe the differences between these algorithms and offers only general advice.

Also, for the return type, it does not tell me how to determine the size of the varbinary. If the input is a 15-character password, how many bytes will the output be?
History
That test is invalid
The test using REPLICATE is not valid since REPLICATE has an 8000 byte limit. The reason the results are the same for REPLICATE ('A', 8000) and REPLICATE ('A', 9000) is because each will generate 8000 A's. If you actually try to process more that 8000 bytes with HashBytes you do get an error.
History
Not 100%

Review this link before using HashBytes or CHECKSUM

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&tid=ee5a601f-145d-4fbf-8343-dfce6a3c196a&cat=&lang=&cr=&sloc=&p=1

They are not 100% guaranteed functions for data compare.

[mdc] The odds of SHA-1 generating a collision are miniscule. The odds of generating a meaningful collision are significantly smaller than that. CHECKSUM, on the other hand, has a very high probability of generating collisions.

History
HashBytes doesn't support large inputs!!!
The documentation is wrong: when the @input is longer than 8000 bytes, the following error is generated:

Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273429

[Tai Yee -- MSFT] Thanks, I have submitted this feedback to the SQL Server documentation team.

See following - :

[mdc] Effect on input longer than 8,000 bytes.

DECLARE @v varchar(max);
SET @v = REPLICATE ('A', 9000); -- Input longer than 8,000 bytes
SELECT HashBytes('SHA1', @v);

SET @v = REPLICATE ('A', 8000); -- Input exactly 8,000 bytes
SELECT HASHBYTES('SHA1', @v);


The output, shown below, is the same hash value twice. This indicates that the two strings that were actually hashed were exactly the same. No error was generated by this code.

0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9
0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9
History
how encrypt "ñ" character? (collate modern spanish)
in sql 2005 "f37c6f3896b2c85fbbd01ae32e47b43f"
in javascript "edb907361219fb8d50279eabab0b83b1"
asp "edb907361219fb8d50279eabab0b83b1"
c# "edb907361219fb8d50279eabab0b83b1"
c++ "edb907361219fb8d50279eabab0b83b1"
History