title: TSQL error occurs depending on number of rows returned in PIVOT
body: <p>I stumbled upon very weird behaviour when using PIVOT in MSSQL Server 2016. I'm trying to retrieve data using PIVOT and get an error depending on how many rows are returned from the query.</p>
  
  <p>Below is query that I wrote specifically to reproduce this error.</p>
  
  <p>I will now add that I know of row size restrictions including knowledge that <code>nvarchar(max)</code> type pointers are also subject to these limitations.
  However:</p>
  
  <ol>
  <li>It seems that in this case error occurs as a result of some inner workings of PIVOT statement and its use of temporary tables (guessing)</li>
  <li>I do not understand how row size limitation is related to number of rows returned by select statement.</li>
  </ol>
  
  <p>I would very much like to understand this error in order to write queries that would avoid this issue. It might also be some sort of bug but I presume that it would be fixed by now so probably not a bug.</p>
  
  <p>Error I get is:</p>
  
  <blockquote>
    <p>Msg 511, Level 16, State 1 Cannot create a row of size XXXX which is
    greater than the allowable maximum row size of 8060.</p>
  </blockquote>
  
  <p>Here are few parameters values combinations that I've tested (first query). Error occurs when values are over limits specified below:</p>
  
  <ul>
  <li>When NoOfGroupKeys is 1 then NoOfFieldNames can be up to 615</li>
  <li>When NoOfGroupKeys is 2 then NoOfFieldNames can be up to 308</li>
  <li>When NoOfGroupKeys is 3 and more (checked up to 100) then NoOfFieldNames can be up to 307</li>
  </ul>
  
  <p>When FieldValue type is changed to <code>nvarchar(4000)</code> or FieldName type is changed to <code>int</code> (second query) error does not occur and I was able to successfully run query with 4096 columns filled with 64KB of data each.</p>
  
  <p>First query:</p>
  
  <pre><code>CREATE TABLE PvErrorTest (
      GroupKeyID      INT                 NOT NULL
      ,FieldName      NVARCHAR    (50)    NOT NULL
      ,FieldValue     NVARCHAR    (MAX)   NULL
      ,UNIQUE (
          GroupKeyID
          ,FieldName
      )
  )
  GO
  
  DECLARE
  -- When NoOfGroupKeys is 1 then NoOfFieldNames can be up to 615
  -- When NoOfGroupKeys is 2 then NoOfFieldNames can be up to 308
  -- When NoOfGroupKeys is 3 and more (checked up to 100) then NoOfFieldNames can be up to 307
      @NoOfGroupKeys      INT     = 2
      ,@NoOfFieldNames    INT     = 309
  
  -- Data in FieldValue doesn't seem to affect this test
      ,@FieldValue        NVARCHAR    (MAX)   = NULL--REPLICATE('X', 8000)
      --SET @FieldValue += @FieldValue
      --SELECT DATALENGTH(@FieldValue)
  
  DECLARE @_GroupKeys TABLE (
      GroupKeyID          INT     NOT NULL
          PRIMARY KEY CLUSTERED
  )
  
  DECLARE @_FieldNames TABLE (
      FieldName           NVARCHAR    (50)    NOT NULL
          UNIQUE
  )
  
  SET NOCOUNT ON
  
  DECLARE
      @GKID       INT                 = 0
      ,@FNID      INT
      ,@PVC       NVARCHAR    (MAX)   = ''
  WHILE @GKID &lt; @NoOfGroupKeys
  BEGIN
      SET @GKID += 1
      INSERT INTO @_GroupKeys (GroupKeyID)
      VALUES (@GKID)
  END
  
  SET @FNID = 0
  WHILE @FNID &lt; @NoOfFieldNames
  BEGIN
      SET @FNID += 1
      INSERT INTO @_FieldNames (FieldName)
      VALUES ('FN_' + CAST(@FNID AS NVARCHAR(50))) 
  
      SET @PVC += ',[FN_' + CAST(@FNID AS NVARCHAR(MAX)) + ']'
  END
  
  INSERT INTO PvErrorTest (GroupKeyID, FieldName, FieldValue)
  SELECT
      GroupKeyID      = GK.GroupKeyID
      ,FieldName      = FN.FieldName
      ,FieldValue     = @FieldValue--NULL--NEWID()
  FROM
      @_GroupKeys                 AS GK
      CROSS JOIN @_FieldNames     AS FN
  
  SET NOCOUNT OFF
  
  DECLARE @_Query NVARCHAR(MAX) = '
  SELECT
      PV.GroupKeyID
      ' + @PVC + '
  FROM
      (SELECT GroupKeyID, FieldName, FieldValue FROM PvErrorTest)
                              AS PVIN
      PIVOT
      (
          MAX(PVIN.FieldValue)
          FOR PVIN.FieldName IN ([FN_0]' + @PVC + ')
      )                       AS PV
  '
  
  EXEC sp_executesql
      @_Query
  GO
  
  DROP TABLE PvErrorTest
  GO
  </code></pre>
  
  <p>Second query:</p>
  
  <pre><code>CREATE TABLE PvErrorTest (
      GroupKeyID      INT                 NOT NULL
      ,FieldName      INT                 NOT NULL
      ,FieldValue     NVARCHAR    (MAX)   NULL
      ,PRIMARY KEY CLUSTERED (
          GroupKeyID
          ,FieldName
      )
  )
  GO
  
  DECLARE
  -- When FieldName is of INT type, error does not occur
      @NoOfGroupKeys      INT                 = 4
      ,@NoOfFieldNames    INT                 = 4095
  
  -- Data in FieldValue doesn't seem to affect this test
      ,@FieldValue        NVARCHAR    (MAX)   = REPLICATE('X', 8000)
  
  DECLARE @_GroupKeys TABLE (
      GroupKeyID          INT     NOT NULL
          PRIMARY KEY CLUSTERED
  )
  
  DECLARE @_FieldNames TABLE (
      FieldName           INT     NOT NULL
          PRIMARY KEY CLUSTERED
  )
  
  SET NOCOUNT ON
  
  DECLARE
      @GKID       INT                 = 0
      ,@FNID      INT
      ,@PVC       NVARCHAR    (MAX)   = ''
  WHILE @GKID &lt; @NoOfGroupKeys
  BEGIN
      SET @GKID += 1
      INSERT INTO @_GroupKeys (GroupKeyID)
      VALUES (@GKID)
  END
  
  SET @FNID = 0
  WHILE @FNID &lt; @NoOfFieldNames
  BEGIN
      SET @FNID += 1
      INSERT INTO @_FieldNames (FieldName)
      VALUES (@FNID) 
  
      SET @PVC += ',[' + CAST(@FNID AS NVARCHAR(MAX)) + ']'
  END
  
  INSERT INTO PvErrorTest (GroupKeyID, FieldName, FieldValue)
  SELECT
      GroupKeyID      = GK.GroupKeyID
      ,FieldName      = FI.FieldName
      ,FieldValue     = @FieldValue
  FROM
      @_GroupKeys                 AS GK
      CROSS JOIN @_FieldNames     AS FI
  
  SET NOCOUNT OFF
  
  
  
  DECLARE @_Query NVARCHAR(MAX) = '
  SELECT
      PV.GroupKeyID
      ' + @PVC + '
  FROM
      (SELECT GroupKeyID, FieldName, FieldValue FROM PvErrorTest)
                              AS PVIN
      PIVOT
      (
          MAX(PVIN.FieldValue)
          FOR PVIN.FieldName IN ([0]' + @PVC + ')
      )                       AS PV
  '
  
  EXEC sp_executesql
      @_Query
  GO
  
  DROP TABLE PvErrorTest
  GO
  </code></pre>
  
  <p>I've now tested this on MSSQL Server 2008 R2 and got somewhat similar results:</p>
  
  <ol>
  <li>When NoOfGroupKeys is 1 then NoOfFieldNames can be up to 620</li>
  <li>When NoOfGroupKeys is 2 then NoOfFieldNames can be up to 311</li>
  <li>When NoOfGroupKeys is 3 and more then NoOfFieldNames can be up to 307</li>
  </ol>
  
  <p>Changing FieldName type or FieldValue type prevents error from occuring.</p>
  
  <p>I really hope there is someone that can shed some light on this behaviour.</p>