HashBytes function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HashBytes function

Author

Topic

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-07 : 08:58:07

i was having problem with using hashbytes function, does it works with nvarchar(max) datatype, when string given is more than 4000 characters long.

following code gives the error.
Msg 8152, Level 16, State 10, Line 7
String or binary data would be truncated.

declare @test nvarchar(max)
set @test = N''
set @test = replicate(N'x', 4000)
set @test = @test+ replicate(N'x', 4000)
set @test = @test+ replicate(N'x', 4000)
print len(@test)
select hashbytes('sha1',@test)

When solution is simple, God is answering….

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 09:05:13

For education purposes, I took the liberty to copy the section in Books Online about HASHBYTES function

quote:
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)

Peter Larsson
Helsingborg, Sweden

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-07 : 09:12:13

Hi Peter,

can it be used with nvarchar(max) datatype as input when data is more that 4000 chars, i am getting avove error...

Many thanks,
Hrishikesh

When solution is simple, God is answering….

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 09:15:19

It seems that SQL Server does not work with VARCHAR(MAX) or NVARCHAR(MAX) the way we expect it to



-- Works
declare @test nvarchar(max)
select @test = replicate('x', 4000) 			-- Works
print len(@test)
select hashbytes('sha1',@test)				-- Works

-- Bug
declare @test nvarchar(max)
select @test = replicate('x', 4000)			-- Works
select @test = @test + replicate('x', 4000) 		-- Works
print len(@test)
select hashbytes('sha1',@test)				-- Bugs out

-- Works
declare @test2 nvarchar(1)
select	@test2 = 'x'
select hashbytes('sha1', replicate(@test2, 400000))	-- Works!!
select hashbytes('sha1', replicate(@test2, 8000))	-- Works

-- Bug
declare @test3 nvarchar(max)
select	@test3 = 'x'
select hashbytes('sha1', replicate(@test3, 4000))	-- Works
select hashbytes('sha1', replicate(@test3, 4001))	-- Does not work

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 09:20:22

The hash difference in the third case produces the same code.
The hash is only calculated for the first 8000 bytes.

Peter Larsson
Helsingborg, Sweden

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-08 : 04:57:08

Hi friends,

Thatz what i had noticed, its just ignores anything greater that 4000 chars for nvarchar. I was thinking of using it to detect changes in row instead of binary_checksum.

Binary_checksum generates lot of collisions for my liking.

Any idea how can i use hashbytes for above problem?

or is there any other approch?

Many thanks,
Hrishikesh

When solution is simple, God is answering….

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:08:36

This works, kind of. For the first 8000 bytes.

declare @test nvarchar(max),
	@t1 nvarchar(max),
	@t2 nvarchar(max)

select	@t1 = N'Peso',
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1,
	@t1 = @t1 + @t1 + @t1 + @t1
print len(@t1)

select	@t2 = N'Peso',
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2,
	@t2 = @t2 + @t2 + @t2 + @t2
print len(@t2)

select @test = @t1 + @t2
print len(@test)

select hashbytes('sha1', left(@test, 4000))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-08 : 05:36:46

This is how you can overcome the 8000 bytes limit for REPLICATE function.
But the HASHBYTES function still only accepts 8000 bytes.

Peter Larsson
Helsingborg, Sweden

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-08 : 06:12:58

Hi Peso,

Thanks a lot for the reply, Most of our tables would have more than 50 fields (300 being highest), with most being nvarchar datatype, so the length of row would be more that 4000 chars (nvarchar) for most of records, so above solution may not work.

Many thanks,
Hrishikesh

When solution is simple, God is answering….

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-08 : 06:30:55

Hi Peso,

DO you think i can use EncryptByPassPhrase as an option?

Many Thanks
Hrishikesh

When solution is simple, God is answering….

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-02-12 : 04:32:52

Hi Peso,

I tried that as well, there is also limit of 4000 chars.

Many thanks.

When solution is simple, God is answering….

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources