25

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?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Alexander
  • 19,906
  • 19
  • 75
  • 162

1 Answers1

52

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
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    I have switched the schema to `nvarchar(max)` and the query works. Thanks for pointing out that `text` is obsolete. – Alexander Jan 20 '16 at 10:46
  • 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? – Alexander Jan 20 '16 at 10:50
  • @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. http://stackoverflow.com/questions/2133946/nvarcharmax-vs-ntext – Lukasz Szozda Jan 20 '16 at 10:53
  • 32K-64K would be sufficient, but I think varchar takes no number exceeding 4000. – Alexander Jan 20 '16 at 10:58
  • 1
    Fantastic. Thank you – Vinyl Warmth Feb 01 '19 at 16:35