8

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.

11

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:

SELECT s.name, o.name
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE UPPER(m.definition) LIKE N'%UPDATETEXT%'
     OR UPPER(m.definition) LIKE N'%WRITETEXT%'
     OR UPPER(m.definition) LIKE N'%READTEXT%'
     OR UPPER(m.definition) LIKE N'%TEXTPTR%'
     OR UPPER(m.definition) LIKE N'%TEXTSIZE%';

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:

SELECT DISTINCT s.name, o.name
  FROM sys.parameters AS p
  INNER JOIN sys.objects AS o
  ON p.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE system_type_id IN (34,35,99);

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.

2

We've gone through this without any issues. Anywhere you are updating or inserting data, make sure that is a traditional insert/update and that you aren't using WRITETEXT or UPDATETEXT.
Other than that, everything should work fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.