SQL Server 2008和HashBytes [英] SQL Server 2008 and HashBytes
问题描述
我有一个很大的nvarchar，我希望传递给HashBytes函数。
我得到错误：
字符串或二进制文件会被截断
无法将NULL值插入
列'colname'，tbale'table';
列不允许为空值UPDATE
失败。语句已被
终止。
我发现这是由于HashBytes函数的最大限制为8000字节。进一步的搜索向我展示了一个'解决方案'，在这里我的大型varchar将被分割和散列，然后再与这个用户定义的函数结合：
function [dbo]。[udfLargeHashTable]（@algorithm nvarchar（4），@InputDataString varchar（MAX））
RETURNS varbinary（MAX）
AS
BEGIN
DECLARE
@Index int，
@InputDataLength int，
@ReturnSum varbinary（max），
@InputData varbinary（max）
SET @ReturnSum = 0 $ b @ b SET @Index = 1
SET @InputData = convert（binary，@ InputDataString）
SET @InputDataLength = DATALENGTH（@InputData）
WHILE @Index< = @InputDataLength
BEGIN
SET @ReturnSum = @ReturnSum + HASHBYTES（@algorithm，SUBSTRING（@InputData，@Index，8000））
SET @Index = @Index + 8000
END
RETURN @ReturnSum
END
我打电话给：
set @ ReportDefinitionHash = convert（int，dbo。[udfLargeHashTable]（'S HA1'，@ ReportDefinitionForLookup））
其中@ReportDefinitionHash是int，@ReportDefinitionForLookup是varchar
传递一个简单的字符'test'会产生与我的UDF不同的int，而不是正常的HashBytes调用。
关于这个问题的任何建议？
只要使用这个函数（取自）：
create function dbo.fn_hashbytesMAX
（@string nvarchar（max）
，@Algo varchar（10）
）
返回varbinary（20）
为
/ ***************************** *******************************
*
*作者：Brandon Galderisi
*最后修改：15-SEP-2009（由Denis）
*目的：使用系统功能hbytes以及
*作为sys.fn_varbintohexstr来分割
* nvarchar（max）字符串和散列在8000字节中
*块散列每个8000字节块，
*获得40字节输出，将每个
* 40字节的输出流输出到一个字符串中，然后散列
*该字符串。
*
***************************************** ******************** /
begin
declare @concat nvarchar（max）
，@ NumHash int
， @HASH varbinary（20）
set @NumHash = ceiling（（datalength（@string）/ 2）/（4000.0））
/ * HashBytes只支持8000字节，所以如果字符串较大， /
if @ NumHash> 1
begin
- ＃* 4000字符串
; with a（选择1作为n union all选择1） - 2
，b as（从a，a1选择1作为n） - 4
，c as（从b选择1作为n，b b1） - 16
，d as（select 1 as n从c，c c1） - 256
，e as（从d中选择1作为n，d d1） - 65,536
，f as（从e中选择1作为n，e e1） - - 4,294,967,296 = 17+ TRILLION字符
，计为（选择row_numb $（$）
，因子为（选择rn，（rn * 4000）+1因子分解）
选择@concat = cast（（
select right（sys.fn_varbintohexstr
（
hashbytes（@Algo，substring（@string，factor - 4000，4000））
）
，40）+' '
来自因素
其中rn <= @NumHash
用于xml路径（''）
）as nvarchar（max））
set @HASH = dbo.fn_hashbytesMAX（@concat，@ Algo）
end
else
begin $ b $ set @HASH = convert（varbinary（20），hashbytes（@Algo ，@string））
end
return @HASH
end
结果如下：
select
hashbytes（'sha1'，N'test '） - 带nvarchar输入
的原生函数，hashbytes（'sha1'，'test ） - 使用varchar输入的原生函数
，dbo.fn_hashbytesMAX（'test'，'sha1'） - 加入到nvarchar输入的函数
，dbo.fnGetHash（'sha1'，'test' ） - 您的功能
输出：
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0xA94A8FE5CCB19BA61C4C0873D391E987982FBBD3
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0x00000000AE6DBA4E0F767D06A97038B0C24ED720662ED9F1
I have quite a large nvarchar which I wish to pass to the HashBytes function. I get the error:
"String or binary would be truncated. Cannot insert the value NULL into column 'colname', tbale 'table'; column does not allow nulls. UPDATE fails. The statement has been terminated."
Being ever resourceful, I discovered this was due to the HashBytes function having a maximum limit of 8000 bytes. Further searching showed me a 'solution' where my large varchar would be divided and hashed seperately and then later combined with this user defined function:
function [dbo].[udfLargeHashTable] (@algorithm nvarchar(4), @InputDataString varchar(MAX))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE
@Index int,
@InputDataLength int,
@ReturnSum varbinary(max),
@InputData varbinary(max)
SET @ReturnSum = 0
SET @Index = 1
SET @InputData = convert(binary,@InputDataString)
SET @InputDataLength = DATALENGTH(@InputData)
WHILE @Index <= @InputDataLength
BEGIN
SET @ReturnSum = @ReturnSum + HASHBYTES(@algorithm, SUBSTRING(@InputData, @Index, 8000))
SET @Index = @Index + 8000
END
RETURN @ReturnSum
END
which I call with:
set @ReportDefinitionHash=convert(int,dbo.[udfLargeHashTable]('SHA1',@ReportDefinitionForLookup))
Where @ReportDefinitionHash is int, and @ReportDefinitionForLookup is the varchar
Passing a simple char like 'test' produces a different int with my UDF than a normal call to HashBytes would produce.
Any advice on this issue?
Just use this function (taken from Hashing large data strings with a User Defined Function):
create function dbo.fn_hashbytesMAX
( @string nvarchar(max)
, @Algo varchar(10)
)
returns varbinary(20)
as
/************************************************************
*
* Author: Brandon Galderisi
* Last modified: 15-SEP-2009 (by Denis)
* Purpose: uses the system function hashbytes as well
* as sys.fn_varbintohexstr to split an
* nvarchar(max) string and hash in 8000 byte
* chunks hashing each 8000 byte chunk,,
* getting the 40 byte output, streaming each
* 40 byte output into a string then hashing
* that string.
*
*************************************************************/
begin
declare @concat nvarchar(max)
,@NumHash int
,@HASH varbinary(20)
set @NumHash = ceiling((datalength(@string)/2)/(4000.0))
/* HashBytes only supports 8000 bytes so split the string if it is larger */
if @NumHash>1
begin
-- # * 4000 character strings
;with a as (select 1 as n union all select 1) -- 2
,b as (select 1 as n from a ,a a1) -- 4
,c as (select 1 as n from b ,b b1) -- 16
,d as (select 1 as n from c ,c c1) -- 256
,e as (select 1 as n from d ,d d1) -- 65,536
,f as (select 1 as n from e ,e e1) -- 4,294,967,296 = 17+ TRILLION characters
,factored as (select row_number() over (order by n) rn from f)
,factors as (select rn,(rn*4000)+1 factor from factored)
select @concat = cast((
select right(sys.fn_varbintohexstr
(
hashbytes(@Algo, substring(@string, factor - 4000, 4000))
)
, 40) + ''
from Factors
where rn <= @NumHash
for xml path('')
) as nvarchar(max))
set @HASH = dbo.fn_hashbytesMAX(@concat ,@Algo)
end
else
begin
set @HASH = convert(varbinary(20), hashbytes(@Algo, @string))
end
return @HASH
end
And the results are as following:
select
hashbytes('sha1', N'test') --native function with nvarchar input
,hashbytes('sha1', 'test') --native function with varchar input
,dbo.fn_hashbytesMAX('test', 'sha1') --Galderisi's function which casts to nvarchar input
,dbo.fnGetHash('sha1', 'test') --your function
Output:
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0xA94A8FE5CCB19BA61C4C0873D391E987982FBBD3
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0x00000000AE6DBA4E0F767D06A97038B0C24ED720662ED9F1
这篇关于SQL Server 2008和HashBytes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
