title: SQL NVARCHAR and VARCHAR Limits
body: <p>All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for <code>NVARCHAR(MAX)</code>, but looking at the executed SQL in Server Profiler for the statement </p>
  
  <pre><code>DELARE @SQL NVARCHAR(MAX);
  SET @SQL = 'SomeMassiveString &gt; 4000 chars...';
  EXEC(@SQL);
  GO
  </code></pre>
  
  <p>Seems to work(!?), for another query that is also large it throws an error which is associated with this 4000 limit(!?), it basically trims all of the SQL after this 4000 limit and leaves me with a syntax error. Despite this in the profiler, it is showing this dynamic SQL query in <strong>full</strong>(!?). </p>
  
  <p>What exactly is happening here and should I just be converting this @SQL variable to VARCHAR and get on with it?</p>
  
  <p>Thanks for your time.</p>
  
  <p>Ps. It would also be nice to be able to print out more than 4000 chars to look at these big queries. The following are limited to 4000 </p>
  
  <pre><code>SELECT CONVERT(XML, @SQL);
  PRINT(@SQL);
  </code></pre>
  
  <p>is there any other cool way?</p>