SQL Server – ntext cannot be selected as DISTINCT

I needed to make a distinct result set from a table that contained ntext, and I got the error message below;

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

The query (this is from Northwind) looks like this (I know, here I don’t need the distinct, but this is an example);

select distinct * from Suppliers

What to do? The solution is to cast the ntext fields to some other field type that we can perform distinct against, for instance varchar(1000). This means we loose text in the result set – if there is more text than 1000 characters in the column we’re casting. But that’s the price we’ll have to pay;