TSQL error occurs depending on number of rows returned in PIVOT

Question

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.

Below is query that I wrote specifically to reproduce this error.

I will now add that I know of row size restrictions including knowledge that nvarchar(max) type pointers are also subject to these limitations. However:

1. 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)
2. I do not understand how row size limitation is related to number of rows returned by select statement.

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.

Error I get is:

Msg 511, Level 16, State 1 Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060.

Here are few parameters values combinations that I've tested (first query). Error occurs when values are over limits specified below:

• 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

When FieldValue type is changed to nvarchar(4000) or FieldName type is changed to int (second query) error does not occur and I was able to successfully run query with 4096 columns filled with 64KB of data each.

First query:

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 < @NoOfGroupKeys
BEGIN
    SET @GKID += 1
    INSERT INTO @_GroupKeys (GroupKeyID)
    VALUES (@GKID)
END

SET @FNID = 0
WHILE @FNID < @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

Second query:

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 < @NoOfGroupKeys
BEGIN
    SET @GKID += 1
    INSERT INTO @_GroupKeys (GroupKeyID)
    VALUES (@GKID)
END

SET @FNID = 0
WHILE @FNID < @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

I've now tested this on MSSQL Server 2008 R2 and got somewhat similar results:

1. When NoOfGroupKeys is 1 then NoOfFieldNames can be up to 620
2. When NoOfGroupKeys is 2 then NoOfFieldNames can be up to 311
3. When NoOfGroupKeys is 3 and more then NoOfFieldNames can be up to 307

Changing FieldName type or FieldValue type prevents error from occuring.

I really hope there is someone that can shed some light on this behaviour.