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
thank you,
this solution solve my problem
best regards
Thanks! I needed this solution, it was very helpfull.
nice solution
To get rid of the performance issue read the following link:
http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
you are the man. thanks for this…. 2012, 4 years on and this stuff is still good to go.
Hello There. I found your blog using msn.
This is a really well written article. I will make sure to bookmark it and come back to read more
of your useful info. Thanks for the post. I’ll certainly comeback.
Thank you. I really appreciate your feedback.
Regards
You saved me, thanks!
Thank you :) It works for me too