How to switch from VARCHAR to TEXT in SQL 2000?

Question

What do I need to consider before I switch a bunch of fields from VARCHAR(bignumber) to TEXT?

Aside from performance, and sometime in the far future TEXT will be deprecated, and aside from the fact that it looks like I need to drop and recreate the table to alter the column's data type?

This is for SQL 2000-- I can't do VARCHAR(max) and VARCHAR(8000) isn't large enough.

Answer 1

At least:

• Almost no string functions
• Can't be used in AFTER triggers
• Not as local variables
• Hard to manipulate (eg have to use pointers)
• Comparison is a pain (from devio)

Back in ye olden SQL Server 6.5 days, I stored some data in several varchar(255) columns because varchar(8000) was only introduced with SQL Server 7.