title: Subquery casting as uniqueidentifier failes without Top() - MSSQL
body: <p>The following query excludes rows by Id (of type uniqueidentifier) returned from a subquery, by trimming a NVARCHAR(MAX) field to a valid uniqueidentifier value, like <em>xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx</em>).</p>
  <pre><code>select * from tableA 
  where Id not in(select cast (right(message, 36) as uniqueidentifier) from tableB)
  </code></pre>
  <p>But, the query above results in an error:
  <a href="https://i.stack.imgur.com/31q2B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/31q2B.png" alt="Error without TOP" /></a></p>
  <p>The same problem occurs, if I use CONVERT.</p>
  <p>The subquery</p>
  <pre><code>select cast (right(message, 36) as uniqueidentifier) from tableB
  </code></pre>
  <p>itself works.</p>
  <p>My first attempt was to check, if there are some rows that are invalid by using TOP.
  As I added a TOP to the query it just works fine in every case:</p>
  <pre><code>select * from tableA 
  where Id not in (select top(n) cast (right(message, 36) as uniqueidentifier) from tableB)
  </code></pre>
  <p>The query works fine no matter what value I take for <em>n</em> (1, 1.000 or even 100.000)
  I also thought about using TOP with ORDER BY. But when all rows are returned, using TOP(100000), the query works as intended.</p>
  <p>Therefore, there are no NULL entries or not valid rows that are trimmed to an invalid uniqueidentifier value in TableB. The overall number of rows in TableB is about 13.000...</p>
  <p>I found a similar question <a href="https://stackoverflow.com/questions/26971082/strange-cast-behaviour-in-sql-server-2008-r2">here</a>, but it doesn't quite matches my problem.</p>
  <p>Why does the query works after using top? I hope you can help me to understand this behaviour.</p>