The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

Question

I have a table

• Id (PK)
• Owner int
• DescriptionText text

which is joined to another table

• Id (FK)
• Participant int

The Owner can be a Participant, and if it is, the same reference (into user table) is in Owner and Participant. So I did:

SELECT TableA.Id,TableA.Owner,TableA.Text
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner.TableA.Text
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User

This query should return all distinct data sets where a certain @User is either Owner or Participant or both.

And it would, if SQL Server wouldn't throw

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Since Id is a PK, and Text is from the same table, why would SQL Server want to compare Text at all?

I can use UNION ALL to stop duplicate detection, but can I circumvent this without losing the distinctness of the results?

Solution

Correct way

Stop using TEXT it is obsolete. Alter table schema.

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.

Workaround

Cast to NVARCHAR(MAX):

SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA
WHERE TableA.Owner=@User
UNION
SELECT TableA.Id,TableA.Owner, CAST(TableA.DescriptionText AS NVARCHAR(MAX))
FROM TableA LEFT JOIN TableB ON (TableA.Id=TableB.Id)
WHERE TableB.Participant = @User

Comments

[Clockwork-Muse]

Hey, is your dataset distinct otherwise? Only one owner of a table, and only registered as a participant once?

[Alexander]

I have switched the schema to nvarchar(max) and the query works. Thanks for pointing out that text is obsolete.

[Alexander]

By the way, is there any disadvantage/drawback of the recommended data types over the obsoleted ones that I should be aware of? Is nvarchar(MAX) counting towards maximum row size, do they impact indexing or query speed?

[Lukasz Szozda]

@Alexander I would say that newer datatypes are better. TEXT is available only because backward compatibility. You should use MAX when it is needed. For example does your description really needs 2GB text? Maybe 4000 will be absolutely sufficient. stackoverflow.com/questions/2133946/nvarcharmax-vs-ntext

[Alexander]

32K-64K would be sufficient, but I think varchar takes no number exceeding 4000.

[Vinyl Warmth]

Fantastic. Thank you