3

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?

0

1 Answer 1

3

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:

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

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

    3. Removes the old column from the table schema.

    4. 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.

1
  • Yes, absolutely they were created (via select into as shorthand) normal then switched to sparse. Alter table rebuild fixes my issue. I wish there was a way to make SQL server report what it thinks the breakdown of the row is! I will save your undocumented query sql for the future.
    – tim654321
    Sep 4, 2023 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.