Technical Article

Script To Compute Checksum Of The Text Field

,

It is a known fact, that checksum function can not be applied to text, ntext and image fields. But sometimes it is necessary to compare whether a value in a text field has changed (although it is not guaranteed that checksum generates a unique value, but it still could be used as a good estimator). This script computes checksum of the text field (has not been tested with ntext or images!). I think someone might find it useful. The only concern is that it does not perform greatly on the large strings (>1MB). Relatively small strings would provide a tolerable performance. But if one has to compare rows of the table field by field using checksums, text fields do not have to be excluded or truncated. The idea behind the function is very simple. It breaks text into chunks of 2000 characters (using substring(…) function). Then it records every chunk of text into a table variable. Identity field of the table variable will maintain correct order of every portion of data. After data splitting is complete, one can compute an aggregate checksum on the table variable using checksum_agg(binary_checksum(*)) expression. This expression is described in SQL Server BOL.

CREATE FUNCTION fn_CheckSum_Text (@data text)
RETURNS int AS
BEGIN
declare @checksum int
declare @length int
declare @dataLength int
declare @start int
declare @count int
declare @rowCount int

set @length = 2000
set @start = 1
set @count = 0

set @dataLength = datalength(@data)
set @rowCount = cast(ceiling(cast(@dataLength as float)/ @length) as int)

declare @text_values table (id int identity(1,1), value varchar(2000))

while @count < @rowCount
begin
if @count + 1 = @rowCount
set @length = @dataLength % @length

insert into @text_values(value) select substring(@data, @start, @length)

set @start = @start + @length
set @count = @count + 1
end

--compute the checksum for the text column
select @checksum = checksum_agg(binary_checksum(*)) from @text_values
return @checksum
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

New MDX Book Published

Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included

2003-05-01

2,698 reads

SQLServerCentral Article

How to Build Dynamic Stored Procedures

Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.8 (5)

You rated this post out of 5. Change rating

2007-10-02 (first published: )

50,618 reads