Given the same input, is the HASHBYTES function guaranteed to return the same output?

Question

If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance?

For example, will

SELECT HASHBYTES('SHA1', 'Hello World')

generate the same output regardless of SQL version, processor architecture etc?

and will

SELECT HASHBYTES('SHA1', column1) from table1

generate the same output if the column definition is slightly different - varchar(50) vs varchar(100) - for example?

Is there anything that needs to be considered here?

Answer 1

In terms of different instances, the HASHBYTES should return the same values if the same script is run, regardless of the server version, processor or architecture:

I've just run the following on both SQL Server 2005 and SQL Server 2012, on completely different hardware configuration. Both have produced the output 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

However, if the collation is different, the output will be different.

In terms of column definition, the output should be the same regardless of the length of the field.

Taking your example of VARCHAR(50) and VARCHAR(100), we can test the output like so:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50))); 
SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

Both SELECT statements return the same value:

0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

However, be aware that VARCHAR and NVARCHAR will not produce the same HASHBYTES value, even with the same string:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50))); -- Returns 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA
SELECT  HASHBYTES('SHA1', CAST('Test' AS NVARCHAR(100))); -- Returns 0x9AB696A37604D665DC97134DBEE44CFE70451B1A 

Answer 2

Have similar problem with HASHBYTES. Same values from one "varchar(max)" column generate multiple hash values.

The problem is caused by capitalization and Case Insensitive collation. Because of that my values were actually different.

If you do not care about case sensitivity you can change your values to upper case to mitigate that problem:

SELECT hashbytes('SHA2_512',UPPER('Hello World'))
UNION 
SELECT hashbytes('SHA2_512',UPPER('Hello world'))