title: The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable
body: <p>I have a table</p>
  
  <ul>
  <li>Id (PK)</li>
  <li>Owner int</li>
  <li>DescriptionText text</li>
  </ul>
  
  <p>which is joined to another table</p>
  
  <ul>
  <li>Id (FK)</li>
  <li>Participant int</li>
  </ul>
  
  <p>The Owner can be a Participant, and if it is, the same reference (into user table) is in Owner and Participant. So I did:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This query should return all distinct data sets where a certain @User is either Owner or Participant or both.</p>
  
  <p>And it would, if SQL Server wouldn't throw </p>
  
  <blockquote>
    <p>The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.</p>
  </blockquote>
  
  <p>Since Id is a PK, and Text is from the same table, why would SQL Server want to compare Text at all?</p>
  
  <p>I can use <code>UNION ALL</code> to stop duplicate detection, but can I circumvent this without losing the distinctness of the results?</p>