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,

Advertisements

9 Comments

  1. manafee

    thank you,
    this solution solve my problem

    best regards

  2. Marco

    Thanks! I needed this solution, it was very helpfull.

  3. zadkine

    nice solution

  4. Dave

    you are the man. thanks for this…. 2012, 4 years on and this stuff is still good to go.

  5. 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

  6. Ali

    You saved me, thanks!

  7. toto@yopmail.com

    Thank you :) It works for me too

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: