SQL Server Error Messages - Msg 420

Error Message

Server: Msg 420, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in an ORDER BY clause.

Causes

As the error message suggests, you cannot use a column of text, ntext or image data type as part of the ORDER BY clause in a SELECT statement.

To illustrate, let’s assume that you have the following table in your database:

CREATE TABLE [dbo].[Notes] (
[NotesID]       INT NOT NULL IDENTITY(1,1),
[NotesOwner]    VARCHAR(20),
[NotesText]     TEXT )

You want to get all the records from the [dbo].[Notes] table but instead of sorted by the notes ID (NotesID) or notes owner (NotesOwner), you want the result be sorted by the notes text (NotesText).

SELECT * FROM [dbo].[Notes]
ORDER BY [NotesText]

Issuing this SELECT statement will generate the following error message:

Server: Msg 420, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be used in an ORDER BY clause.

Solution / Work Around

One way to work around this limitation is to CAST or CONVERT the TEXT/NTEXT data type column into either VARCHAR or NVARCHAR data type.

SELECT * FROM [dbo].[Notes]
ORDER BY CAST([NotesText] AS VARCHAR(8000))

If the column is of NTEXT data type, you can CAST it to NVARCHAR, but with only a length of 4000 since this is the maximum length allowed by a NVARCHAR data type:

SELECT * FROM [dbo].[Notes]
ORDER BY CAST([NotesText] AS NVARCHAR(4000))