Still Using text, ntext, image? You’re on Borrowed Time

Nothing has changed about text, ntext, and image in a long time. Microsoft deprecated them in SQL Server 2005, told us to use varchar(max), nvarchar(max), and < code>varbinary(max) instead, and then... let them be. Twenty years of maintenance mode. They still work, the docs still carry the IMPORTANT! callout, and they're still in production schemas everywhere.

I went back to Aaron Bertrand's SQLPerformance piece on these types recently, and this line landed for me: 'fear of removal shouldn't be your only motivator.' He's not worried these will actually disappear any time soon, but that's not the problem. The problem is what they break in your day-to-day, right now, on the box you already manage.

So this post is proactive cleanup. Not because the vendor is coming for you - though you don't know when - but because every one of these columns is already costing you needlessly.

The replacements

Old type	Replacement	For
text	varchar(max)	Non-Unicode strings
ntext	nvarchar(max)	Unicode strings
image	varbinary(max)	Binary blobs

Same 2GB upper bound. Better behavior. No reason not to.

Demo

USE tempdb;
GO
CREATE TABLE dbo.OldSchool
(
    id          INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    notes       TEXT,
    description NTEXT,
    payload     IMAGE
);
GO

INSERT dbo.OldSchool (notes, description, payload)
VALUES ('hello', N'world', 0x01020304);
GO

Reason 1: Half the string functions reject you outright

Try a basic LEN():

SELECT LEN(notes) FROM dbo.OldSchool;

Msg 8116, Level 16, State 1
Argument data type text is invalid for argument 1 of len function.

Same story for LEFT, RIGHT, RTRIM, UPPER, most comparison operators, DISTINCT, GROUP BY, ORDER BY. To modify the data you have to fall back on TEXTPTR, WRITETEXT, and UPDATETEXT, which nobody under 40 has ever willingly typed.

Reason 2: They cannot be INCLUDE columns

CREATE NONCLUSTERED INDEX IX_OldSchool_id
ON dbo.OldSchool (id) INCLUDE (notes);

Msg 1999, Level 16, State 1, Line 18
Column 'notes' in table 'dbo.OldSchool' is of a type that is invalid for use 
as included column in an index.

* My errors are carriage-returned so you can see the whole thing.

You lose covering index strategies entirely for any column of these types. With VARCHAR(MAX) you can at least INCLUDE it.

Reason 3: They block ONLINE rebuilds (the AG killer)

This is the one that matters in production. If you have a single text, ntext, or image column anywhere in the table, the clustered index cannot be rebuilt ONLINE. Not the column itself, not the index that contains it — the entire table.

ALTER TABLE dbo.OldSchool REBUILD WITH (ONLINE = ON);

Msg 2725, Level 16, State 2, Line 21
An online operation cannot be performed for index 'PK__OldSchoo__3213E83FB4F1D441' 
because the index contains column 'payload' of data type text, ntext, image or FILESTREAM. 
For a non-clustered index, the column could be an include column of the index. For a clustered 
index, the column could be any column of the table. If DROP_EXISTING is used, the column could 
be part of a new or old index. The operation must be performed offline.

* Definitely carriage-returned this one.

If you maintain Availability Groups, mirroring, or anything that needs 24x7 maintenance windows, this is the line that matters. Your index maintenance job either must skip the table or schedule downtime. There is no third option.

Find them all

Aaron's sys.columns hunt, with the system_type_id values you need to remember: 34 (image), 35 (text), 99 (ntext).

SELECT
    [Schema] = s.name,
    [Table] = o.name,
    [Column] = c.name,
    [Type] = TYPE_NAME(c.system_type_id),
    [Replace With] = 
    	CASE c.system_type_id WHEN 34 THEN N'varbinary(max)'
            WHEN 35 THEN N'varchar(max)'
            WHEN 99 THEN N'nvarchar(max)' END,
    [Nullable]  = c.is_nullable
FROM sys.columns c JOIN sys.objects o 
  ON c.[object_id] = o.[object_id] JOIN sys.schemas s 
    ON o.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (34, 35, 99)
AND o.type = 'U'
ORDER BY s.name, o.name, c.name;

Don't forget the parameter list. Procs and functions hide these types too:

SELECT
    [Schema] = s.name,
    [Object] = o.name,
    [Parameter] = p.name,
    [Type] = TYPE_NAME(p.system_type_id)
FROM sys.objects o JOIN sys.schemas s 
  ON o.[schema_id] = s.[schema_id] JOIN sys.parameters p 
    ON p.[object_id] = o.[object_id]
WHERE p.system_type_id IN (34, 35, 99)
ORDER BY s.name, o.name, p.name;

Generate the ALTER scripts

SELECT
    N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)
  + N' ALTER COLUMN ' + QUOTENAME(c.name) + N' '
  + CASE c.system_type_id
      WHEN 34 THEN N'varbinary(max)'
      WHEN 35 THEN N'varchar(max)'
      WHEN 99 THEN N'nvarchar(max)'
    END
  + CASE c.is_nullable WHEN 0 THEN N' NOT NULL;' ELSE N' NULL;' END
FROM sys.columns c JOIN sys.objects o 
  ON c.[object_id] = o.[object_id] JOIN sys.schemas s 
    ON o.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (34, 35, 99)
AND o.type = 'U'
ORDER BY s.name, o.name, c.name;

Note, just like the ALTER INDEX, the ALTER COLUMN cannot run with ONLINE = ON. Same restriction. This conversion is an outage. You want to communicate it and schedule accordingly.

The DROP COLUMN gotcha

If you decide a column is junk and want to drop it instead of converting it, ntext has a special trap. Per Microsoft's docs, when you drop an ntext column, the cleanup is serialized across every row in the table. On a wide table with millions of rows, this can take hours. If that's you, be sure to schedule appropriately.

The recommendation is to NULL the column first, and then drop:

UPDATE dbo.BigTable SET old_ntext_col = NULL;
GO
ALTER TABLE dbo.BigTable DROP COLUMN old_ntext_col;
GO

Batch the UPDATE if the table is large. Standard log management rules apply.

The bottom line

Despite being deprecated in 2005, they are still present and usable as of SQL Server 2017 and later, although Microsoft advises against using them for new development. Need motivation? The text, ntext, and image columns can cause issues with data cleanup, and switching to MAX types provides better performance. Pretty good reasoning to me.

More to Read

Aaron Bertrand: Deprecated features to take out of your toolbox - Part 3
Microsoft Learn: ntext, text, and image (Transact-SQL)
SQLServerCentral: Deprecated but Forgotten