Transact-SQL Reference


SQL Server 2008 Books Online (October 2009)
HASHBYTES (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions

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

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1
Arguments
'<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.
Return Value

varbinary (maximum 8000 bytes)
Remarks

Allowed input values are limited to 8000 bytes. The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1.
Examples

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

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO
See Also

Other Resources

Choosing an Encryption Algorithm

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

Thomas Lee
Error if >8000
If the input is longer than 8000, it will not be truncated, but an error ("String or binary data would be truncated.") is raised.
Tags : contentbug?

Mike C_1
MD2, MD4, MD5 Considered Insecure
The MD2, MD4 and MD5 hash functions are widely considered insecure hash functions, as it has been proven that meaningful hash collisions can be generated for them. It's recommended to avoid these hash functions for secure applications (such as hashing passwords, etc.) It's recommended instead that you use the more secure SHA family of hash functions for secure hash requirements.
Tags :

Mike C_1
SHA = SHA1
The SHA and SHA1 identifiers are equivalent to this function. There may be some confusion as the term "SHA" without a number behind it is often associated with the original SHA function (sometimes referred to as "SHA-0").
Tags :

Mike C_1
INPUT LONGER THAN 8000 BYTES

Truncation 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.

0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9
0xD2967D6425E56C18BA979EEFB4E0DBD1269D9BC9
Tags :

Page view tracker