If you've stumbled upon this post you're probably dealing with a SQL / T-SQL query which is either doing:
- a DISTINCT on a column having ntext data type.
- a JOIN with multiple queries and multiple tables;
- a UNION with two or more tables;
And you're receiving the following error message:
The ntext data type cannot be selected as DISTINCT because it is not comparable.
The Issue
The problem is caused by the fact that SQL Server's ntext, text, and image data types have been declared obsolete, as stated within this official MSDN post.
Here's the relevant highlight:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Luckily enough, we can overcome the issue in at least two ways.
The Workaround
Identify the column(s) that use the obsolete data type(s) and CAST them to their corresponding accepted types. For example, change this:
|
1 |
SELECT DISTINCT nTextColumn |
into the following:
|
1 |
SELECT DISTINCT CAST (nTextColumn AS NVARCHAR(MAX)) |
The Fix
The permanent and (suggested) solution, to avoid losing valuable time with multiple CASTs and keep your query safe from performance issues, is to permanently change the data type of the column(s) using the obsolete data type(s) into their corresponding accepted data types. In details:
- ntext should become nvarchar(max)
- text should become varchar(max)
- image should become varbinary(max)
That's about it!
I hope that this quick guide will help those who're still struggling with such nasty issue.
