Row greater than max size (8060) but I can't see why

I have a table that has 483 columns.

A date not null, a char(6) not null, and then 481 x float sparse null.

By my maths, even if every sparse float is non-null, the row should still only be:
date: 3 bytes
char(6): 6 bytes
sparse float: 12 bytes x 481
sparse update overhead: 2 bytes x 483
= 6747 bytes and way below the limit of 8060
(even allowing lots for other random overheads)


In fact, the real-life row causing the issue still has 133 of the float columns as nulls and SQL Server claims this row has a row size of 8108 (and is too big).

What is the ghost that is taking up my row space?

What is the ghost that is taking up my row space?

The table contains fixed-length columns which have been dropped. This could happen as part of a data conversion or through iterative development where the tool modifies the existing table structure as the design evolves.

As David Browne pointed out, this could also happen as a result of creating the (fixed length) float columns as non-sparse null initially, then altering them to sparse.

This contradicts the documentation (emphasis added):

-
Changing a column from sparse to non-sparse, or non-sparse to sparse, requires changing the storage format of the column. The SQL Server Database Engine uses the following procedure to accomplish this change:

-
Adds a new column to the table in the new storage size and format.

-
For each row in the table, updates and copies the value stored in the old column to the new column.

-
Removes the old column from the table schema.

-
Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

In fact, the structure is not rebuilt to reclaim space.
Example

CREATE TABLE dbo.T 
(
    d date NOT NULL, 
    c char(6) NOT NULL
);
GO
-- Add a row
INSERT dbo.T (d, c) 
VALUES (CONVERT(date, GETUTCDATE()), 'abcdef');
GO
-- Add 481 float null columns
DECLARE @SQL nvarchar(max) =
(
    SELECT
        STRING_AGG
        (
            CONCAT
            (
                CONVERT(nvarchar(max), N'ALTER TABLE T ADD '),
                FORMAT(SV.number, '\f0'),
                N' float NULL;'
            ),
            SPACE(1)
        )
        WITHIN GROUP (ORDER BY SV.number)
    FROM master.dbo.spt_values AS SV  
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN 1 AND 481
);

EXECUTE (@SQL);

Alter the float columns to sparse:

DECLARE @SQL nvarchar(max) =
(
    SELECT
        STRING_AGG
        (
            CONCAT
            (
                CONVERT(nvarchar(max), N'ALTER TABLE dbo.T '),
                N'ALTER COLUMN ',
                FORMAT(SV.number, '\f0'),
                N' ADD SPARSE;'
            ),
            SPACE(1)
        )
        WITHIN GROUP (ORDER BY SV.number)
    FROM master.dbo.spt_values AS SV  
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN 1 AND 481
);

EXECUTE (@SQL);

Show the dropped columns using an undocumented system view:

SELECT 
    P.[partition_id],
    C.partition_column_id,
    C.max_inrow_length,
    C.leaf_offset
FROM sys.partitions AS P
JOIN sys.system_internals_partition_columns AS C
    ON C.[partition_id] = P.[partition_id]
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.T', 'U')
    AND P.partition_number = 1
    AND C.is_dropped = 1
ORDER BY
    P.[partition_id], 
    C.partition_column_id;

First ten entries only shown for brevity:

partition_id
partition_column_id
max_inrow_length
leaf_offset

72057594129154048
67108865
8
13

72057594129154048
67108866
8
21

72057594129154048
67108867
8
29

72057594129154048
67108868
8
37

72057594129154048
67108869
8
45

72057594129154048
67108870
8
53

72057594129154048
67108871
8
61

72057594129154048
67108872
8
69

72057594129154048
67108873
8
77

72057594129154048
67108874
8
85

Update the row with values for all but 133 float columns:

DECLARE @SQL nvarchar(max) =
(
    SELECT
        CONCAT
        (
            N'UPDATE t SET ',
            STRING_AGG
            (
                CONCAT
                (
                    FORMAT(SV.number, '\f0'),
                    CONVERT(nvarchar(max), N' = RAND()')
                ),
                N','
            )
            WITHIN GROUP (ORDER BY SV.number)
        )
    FROM master.dbo.spt_values AS SV  
    WHERE
        SV.[type] = N'P'
        AND SV.number BETWEEN 1 AND 481 - 133
);

EXECUTE (@SQL);

The error message is:
Msg 511, Level 16, State 1
Cannot create a row of size 8108 which is greater than the allowable
maximum row size of 8060.
The statement has been terminated.


To remove the 'ghost' dropped fixed-length columns, you need to rebuild the table:

ALTER TABLE dbo.T REBUILD;

The previous operation now succeeds.

db<>fiddle demo

Fixed-length columns cannot be cleaned up with DBCC CLEANTABLE.

Dropped variable-length columns do not cause a problem because SQL Server can clean them up or move them off-row to make space when adding a new sparse column.

StackExchange Database Administrators Q#330748, answer score: 3