Converting Deprecated TEXT, NTEXT and IMAGE Columns Safely

An old database can carry large-object types long after the original application is gone. NTEXT and IMAGE columns, along with TEXT, block newer operations. Moving them to MAX types needs a resource plan as well as an ALTER statement.

Inventory TEXT, NTEXT and IMAGE Columns First

Start with catalog metadata across each database you own. sys.columns and sys.types identify the columns; the schema, table, and nullability tell you what the ALTER statement will need. Do not assume that every TEXT column holds short prose. Sample lengths, indexes, constraints, application queries, and dependent modules. An application using old client APIs can break even when the database conversion succeeds.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
       o.name AS table_name, c.name AS column_name,
       ty.name AS current_type, c.is_nullable
FROM sys.columns AS c
JOIN sys.objects AS o ON o.object_id = c.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE o.type = 'U'
  AND ty.name IN ('text','ntext','image')
ORDER BY schema_name,table_name,column_name;

Run this inside each user database; it lists all TEXT, NTEXT and IMAGE columns in user tables. I save the result with the application owner and table size, then choose a small candidate for rehearsal. How will you prove that the application can still read and write the column afterward? A schema inventory alone cannot answer that question.

Match NTEXT and IMAGE Columns to MAX Types

TEXT maps naturally to VARCHAR(MAX), NTEXT to NVARCHAR(MAX), and IMAGE to VARBINARY(MAX). Keep Unicode data Unicode. Switching NTEXT to VARCHAR(MAX) can lose characters and is not a modernization. Preserve the intended NULL or NOT NULL setting in the ALTER statement. If a column is governed by constraints or dependencies, script and test their recreation first.

ALTER TABLE dbo.LegacyDocument
ALTER COLUMN Notes varchar(max) NULL;
ALTER TABLE dbo.LegacyDocument
ALTER COLUMN LocalizedNotes nvarchar(max) NULL;
ALTER TABLE dbo.LegacyDocument
ALTER COLUMN Payload varbinary(max) NULL;

These statements are examples for three hypothetical columns. Verify actual type, nullability, and dependency names before running them. Some changes require a schema modification lock and can run for a long time. Plan a maintenance window based on a restored copy of production, not on the duration in a tiny development database.

Rehearse the Log and Lock Cost

A large ALTER can generate substantial transaction log activity. Check free space in the database files, log file, backup destination, and storage tier. In full recovery, take log backups during a long change as appropriate to your recovery plan; backups allow log reuse only when no other reuse blocker remains. Do not shrink the log afterward as a reflex. Set a sensible size and growth increment for the steady workload.

SELECT name,type_desc,size * 8.0 / 1024 AS size_mb,
       growth,is_percent_growth
FROM sys.database_files;
SELECT log_reuse_wait_desc
FROM sys.databases WHERE database_id = DB_ID();

Record these values before and after the rehearsal. Also measure elapsed time and blocking for the ALTER. I run the test on a restored database with similar row count and large-value distribution. The log behavior of ten short strings says little about a table holding millions of large images.

Move Eligible Small Values in Row

After converting a legacy large-object type to a MAX type, existing values can remain in the old large-object allocation structure. Microsoft documents a follow-up UPDATE of the column to itself to move eligible small values to the newer storage layout. The exact in-row result depends on table options and available row space. The UPDATE is real work; it logs changes and can hold locks. In my test with short values, the LOB pages stayed after the ALTER and almost all of them disappeared after the self-update.

UPDATE dbo.LegacyDocument SET Notes = Notes;
UPDATE dbo.LegacyDocument SET LocalizedNotes = LocalizedNotes;
UPDATE dbo.LegacyDocument SET Payload = Payload;

On a large table, do not run the entire follow-up in a single untested transaction. Rehearse a key-range batch strategy, commit between batches, and track progress by immutable key. A row-by-row loop is slow, while one giant transaction can make recovery and rollback painful. Values larger than the in-row allowance remain off-row, which is expected.

Check What Depends on NTEXT and IMAGE Columns

Search modules for the old column and for conversions written around its limitations. Test full-text indexing, client result mapping, serialization, and write paths. MAX columns can be passed to functions that reject TEXT or NTEXT, but an application can still make assumptions about length or streaming. Watch for ORM migrations that try to recreate the old type on the next deployment.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS module_name
FROM sys.sql_modules
WHERE definition LIKE '%LegacyDocument%';

Text search is only a lead. Dynamic SQL, encrypted modules, application SQL, and external packages will not all appear there. I pair the search with a workload trace or Query Store history and a test run of the application. A successful ALTER is a database result; the migration finishes when dependent behavior works.

Compare Space Before and After

Use sys.dm_db_partition_stats and sys.allocation_units or sp_spaceused to record reserved and used pages before alteration, after alteration, and after the self-update. Space can temporarily rise as old and new structures coexist. An allocation change is not automatically immediate physical file shrinkage. Focus on free pages inside the file and on future backup size rather than forcing a shrink.

EXEC sys.sp_spaceused N'dbo.LegacyDocument';

For a very large table, inspect individual partitions and LOB allocation as well. Rebuild or reorganize only when the measured result justifies it and the operation fits the maintenance window. I document row count, NULL count, representative data checksums, table space, log growth, and application tests. Those numbers let an operator distinguish expected temporary growth from a failed migration.

Roll Out One Table at a Time

Before the rollout, compare sample byte lengths and values using a stable primary key. Export a small set of non-ASCII NTEXT values and large IMAGE values from the restored copy, then read them through the same client path after conversion. A value count alone cannot prove that encoding and binary content survived. Ask the application owner to test both reads and writes, including a NULL and an empty value.

Keep a tested backup and a return plan. Restoring an entire database is rarely an acceptable response to one failed column change, so rehearse the actual rollback strategy for the application. Some conversions are difficult to reverse without data loss or extended downtime. Apply one table, test it, then continue to the next; monitor blocking and log backup health throughout.

The best modernization is boring in production because the surprising parts were found on a restored copy. I write down the pre-change schema and measured resource use, then compare the post-change state against it. The three replacement types are simple to remember. Their operational cost is the part that deserves the longer plan.

Related reading on this blog: How to Find SQL Server Deprecated Features Used by the Application? Interview Question of the Week #165 and Identifying Deprecated SQL Server Features with Extended Events.

A MAX type change is not the whole migration, it is one step before movement and measurement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.