SQL – The ntext data type cannot be selected as DISTINCT because it is not comparable – How to Fix How to fix the "The ntext data type cannot be selected as DISTINCT because it is not comparable" error affecting MS SQL Server and other DBMS based on T-SQL

SQL - The ntext data type cannot be selected as DISTINCT because it is not comparable - How to Fix

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:

into the following:

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.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.