title: size limit for nvarchar(max)
body: <p>i am trying to execute a sql query string which has thousands of characters in t-sql. data type using to hold this query is nvarchar(max). but using this i can hold only 67594 characters. but my query has more characters than this.
  does anybody has any idea why nvarchar(max) is holding up to 67594 characters only? the nvarchar(max) should hold up to 2GB data. isn't it?</p>
  
  <p>the procedure which i am running is:</p>
  
  <pre><code>create procedure test
  (
  @snapshotid1 nvarchar(10),
  @snapshotid2 nvarchar(10),
  @version1 nvarchar(100),
  @version2 nvarchar(100)
  ) AS DECLARE
  @sql nvarchar(max),
  @whereClause1 nvarchar(500),
  @whereClause2 nvarchar(500),
  @fromClause1 nvarchar(100),
  @fromClause2 nvarchar(100)
  
  BEGIN
  set @sql = '';
  set @sql = @sql + N'    
      select v1.v1_brand, version1total, version2total, version1total - version2total as varience from (
      select   "C - Brand" as v1_brand, 
  
      case ' + @period + ' when ''Jan'' then sum(Period1InvoicedAmount)
      when ''Feb'' then
  .
  .
  .
  
  END
  </code></pre>
  
  <p>regards</p>
  
  <p>Subash Amara</p>