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?

Answer 1

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