Tuesday, May 11, 2010

HASHBYTES Function to implement a one way hash

Here I demonstrate using HASHBYTES to generate a hash of a string value.
I've chosen to use SHA1 for this demo which produces an 160-bit (20 byte) hash.

SELECT CAST(REPLICATE('Z',1024) AS NVARCHAR(1024))
-- returns me a string of 1024 'Z' characters

SELECT LEN(CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))
-- clarifies the length is 1024 

SELECT DATALENGTH(CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))
-- shows the real storage length is 2048 (unicode, i.e. Nvarchar requires double byte storage)

SELECT HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))
-- shows the MD5 hash of that Nvarchar string

SELECT LEN(HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024))))
-- confirms the length of that MD5 hash is 20

DECLARE @myhash VARBINARY(20)
SELECT @myhash = HASHBYTES('SHA1', CAST(REPLICATE('Z',1024) AS NVARCHAR(1024)))
-- assigns varbinary hash to variable
In the 'real world' , I'm using this to create indexable key values for long character strings that otherwise can't participate in indexes in this way.

CREATE TABLE [my].[SearchTerms](
 [SearchID] [bigint] IDENTITY(1,1) NOT NULL,
 [SearchTerm] [nvarchar](1024) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [my].[SearchTerms] 
ADD SearchTermHash AS CAST(HASHBYTES('MD5',SearchTerm) AS VARBINARY(20))
GO 

solace : quindecillion

No comments: