I have a SQL Server database which contains a number of image
and text
columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts varbinary(max)
and varchar(max)
.
Aside from changes to application code, my main concern are potential "gotchas" associated with this. For example, is there functionality supported by the older data types but not in the new ones?
Data loss due to truncation at least doesn't seem to be a problem since the new types are at least as large as the old ones.
Best Answer
Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors).
One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like
UPDATETEXT
,READTEXT
,TEXTPTR
,WRITETEXT
,TEXTSIZE
and@@TEXTSIZE
- all of which will likely have to be changed. You can identify those stored in SQL Server this way:Note that this could yield false positives (e.g. those terms may be in a comment or naturally occur in an entity name) and it could miss some (e.g. the commands could be constructed using parameters / dynamic SQL). You're on your own for searching your application codebase and/or source control for instances of the same.
Also make sure you find all modules that accept or output parameters of these types:
You might also want to consider that you may have logic in jobs and other maintenance routines that currently avoid these tables or treat them differently because of the limitations inherent in these data types. When you move to the newer types (and especially on the most modern versions of SQL Server), a lot of these limitations go away.
Finally, other than the syntax above, I can't think of a single feature the old types support that the new types don't.