How to create hash function for NTEXT

I have a table
create table t
(
val ntext(16),
id int not null identity,
hash nvarchar(32)
)
I'd like to avoid rows with double ntext values, thats why I want to compare
hast values only.
But how can I create hash for ntext ?
ThanksOleg,
You could try using checksum_agg after chunking the ntext column
into 8000 byte pieces. Here's an example using image (there is a long
image column in a Northwind table that makes it easier to write a repro).
checksum() and checksum_agg() are not good hash functions, but they
will help you identify rows that may be the same. You will still have to
check to see if they are the same, as you would with any hash function.
create table Integers (
chunk int primary key
)
insert into Integers select ProductID-1 from Northwind..Products
go
create table textVals (
pk int identity primary key,
t image,
hashVal int
)
insert into textVals select photo, null from Northwind..Employees
go
update textVals set
hashVal = (
select checksum_agg(ca) from (
select checksum(substring(tv.t,1+8000*chunk,8000)) as ca
from Integers, textVals as tv
where tv.pk = textVals.pk and chunk <= datalength(t)/8000
) T
)
select datalength(t) as t_len, hashVal from textVals
go
drop table Integers, textVals
-- Steve Kass
-- Drew University
Oleg Cherkasenko wrote:
>I have a table
>create table t
>(
>val ntext(16),
>id int not null identity,
>hash nvarchar(32)
>)
>I'd like to avoid rows with double ntext values, thats why I want to compare
>hast values only.
>But how can I create hash for ntext ?
>Thanks
>
>