The ntext data type cannot be selected as DISTINCT because it is not comparable

Hi All,

Today, I again come up with a very interesting problem. I was working on an SQL Server 2005 DB with almost 160 tables. In one of the core table, there was a field with data type "NTEXT". I wanted to apply DISTINCT on it, as I was getting duplicated records against a join applied to it. After hours I finally got the solution and here it is.

Problem: DISTINCT/GROUP BY keywords don’t work with "NTEXT" data fields. Here’s the error message I got.

"The ntext data type cannot be selected as DISTINCT because it is not comparable"

Solution: Cast NTEXT to NVARCHAR(MAX) so that it can hold the data (will not lose the data) and  then apply DISTINCT. Here’s the example.

    Select DISTINCT cast (Your Col Name as NVARCHAR(MAX))

Drawback: The one of the biggest drawback for casting is against performance. Casting can drastically reduce performance.

I hope this small post can help you and save your many hours.

Kind Regards,

• Posted in: Uncategorized