|
|
Error Message
Server: Msg 421, Level 16, State 1, Line 1
The text/ntext/image data type cannot be selected
as DISTINCT because it is not comparable.
Causes
The text, ntext and image are fixed and variable-length data types used for storing large non-Unicode and Unicode character and binary data. The text data type is used for variable-length non-Unicode data in the code page of the server and with a maximum length of 2,147,483,647 (2^31 – 1) characters.
|
The ntext data type is used for variable-length Unicode data with a maximum length of 1,073,741,823 (2^30 – 1) characters. The image data type is used for variable-length binary data from 0 to 2,147,483,647 (2^31 – 1) bytes.
One of the restrictions of using text, ntext or image data types is that columns defined of these data types cannot be used as part of a SELECT statement that includes the DISTINCT clause. Here’s how this error message can be encountered using the following table structure:
CREATE TABLE [dbo].[Book] (
[BookID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[BookTitle] VARCHAR(200),
[BookSummary] NTEXT,
[BookImage] IMAGE
)
GO
To list all unique book titles and book summaries, the following SELECT statement is used:
SELECT DISTINCT [BookTitle], [BookSummary]
FROM [dbo].[Book]
But since the book summary column is defined as an NTEXT data type, the following error message is generated:
Msg 421, Level 16, State 1, Line 2
The ntext data type cannot be selected as DISTINCT because it is not comparable.
Similarly, to list all unique book titles and book images, the following SELECT statement can be used:
SELECT DISTINCT [BookTitle], [BookImage]
FROM [dbo].[Book]
But just like the previous SELECT statement, since the book image column is defined as an IMAGE data type, the following error message is encountered:
Msg 421, Level 16, State 1, Line 2
The image data type cannot be selected as DISTINCT because it is not comparable.
Solution / Work Around:
As mentioned earlier, there’s a restriction on using columns of text, ntext or image data types in a SELECT statement that contains the DISTINCT clause. Depending on the version of SQL Server being used, there are a few ways of overcoming this restriction.
For SQL Server 2000, a TEXT column can be converted to a VARCHAR data type, an NTEXT column can be converted to an NVARCHAR data type while an IMAGE data type can be converted to VARBINARY data type. The SELECT statements earlier which contain the DISTINCT clause can be rewritten as follows and avoid the error message:
SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(4000)) AS [BookSummary]
FROM [dbo].[Book]
SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(8000)) AS [BookImage]
FROM [dbo].[Book]
For SQL Server 2005 and SQL Server 2008 (and later), instead of limiting the NVARCHAR to 4000 characters or the VARCHAR or VARBINARY to 8000 characters, the MAX specifier can be used in its place, as can be seen in the following SELECT statements:
SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(MAX)) AS [BookSummary]
FROM [dbo].[Book]
SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(MAX)) AS [BookImage]
FROM [dbo].[Book]
If using SQL Server 2005 or SQL Server 2008 (or later), another way of overcoming this restriction and this error message without using the CAST or CONVERT function to convert the text, ntext and image data types to varchar, nvarchar and varbinary, respectively, is to change the data types of the columns to VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). TEXT, NTEXT and IMAGE data types will be removed in a future version of Microsoft SQL Server and use of these data types should be avoided.
|