Skip to main content
further question answering
Source Link
Mark Sinkinson
  • 10.6k
  • 4
  • 46
  • 54

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 

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 

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 
Source Link
Mark Sinkinson
  • 10.6k
  • 4
  • 46
  • 54

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