SQL Server - Comparing text columns

Asked By ISSDBEngr

04-Jun-08 09:19 PM

We are currently using SQL 2005 and I need to create a pipe delimited
text file of over 80 columns daily.  The final dataset is saved as a
text column before sving it to a file as it is larger that varchar
(8000).  If even 1 value in the column changes do I need to include it
in the next day's file.
Is there a way to compare a text column that does not have alot of
overhead.  The data set can be large and it does not seem like
CHECKSUM is a option as text is not supported.
Any help is appreciated.

thanks
Mina

04-Jun-08 09:19 PM

If VARCHAR(8000) isnt' enough and you're using SQL Server 2005,
VARCHAR(MAX) is an option you might consider for the column datatype.

What about tracking the last update date/time for the columns and the
varchar(max) column with a trigger on the update/insert of the columns
and the varchar(max) column?  Another option would be to have the
VARCHAR(Max) column be a calculated column that is always a summary of
the columns in question.

Hope that helps!

02-Jun-08 06:41 PM

ISSDBEngr (ISSDBEngr@gmail.com) writes:

Since you are on SQL 2005, why use text when you can use varchar(MAX)?
That is far less painful than text.

I don't know whether checksum() works on varchars longer than 8000 though.
But checksum() is not very good anyway, you would probably miss a lof
of actual changes. I would run a simple = to compare the values.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx