SQL Server 2014 compression and maximum row size

I need to create wide denormalized table with lot of decimal(26,8) columns (fewer than 1024 column limit, most of the columns would be null or zero).
I know about 8060 bytes per row limitation, so i tried to create table with page compression.
Code below creates table, inserts one row and queries row size. Row size is far below the limit, but if i try to add one more decimal(26,8) columns to table, operation fails with error "Creating or altering table 't1' failed because the minimum row size would be 8074, including 1256 bytes of internal overhead.". Is there any way to create single table with that many columns?

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
with (data_compression = page)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) null';
    execute (@sql);
    set @i += 1;
end;
GO

insert into t1(c1) select 0
GO
declare @i int = 2;
declare @sql varchar(100);
while @i <= 486
begin
    set @sql = 'update t1 set c' + convert(varchar, @i) + ' = 0';
    execute (@sql);
    set @i += 1;
end;
GO

select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
GO

The limit that you're running into has nothing to do with the data stored on the page. The calculation is done based on the data types of the columns. That's why you run into the error without any data in the table. Compression makes this limit worse. You can read about the technical details behind the overhead here.

You can workaround this issue by using SPARSE columns. That means that it'll be possible for inserts to fail depending on what you insert, but you can bypass the 8060 byte limit. The following code shows that you can create 1023 columns just fine:

drop table t1
GO
create table t1(c1 decimal(26, 8) null)
GO

declare @i int = 2;
declare @sql varchar(100);
while @i <= 1023
begin
    set @sql = 'alter table t1 add c' + convert(varchar, @i) + ' decimal(26, 8) SPARSE null';
    execute (@sql);
    set @i += 1;
end;
GO

However, all of the restrictions around it (read the linked article) may make this not suitable for your use case. Specifically, only NULL values (not 0) are optimized to take up very little space. If you try to insert too many 0s in a single row you'll get an error. Here's what I see when I try to insert 1023 0 values:


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

I suppose that if you got really desperate you could create the columns as VARCHAR(27) instead. Variable length columns can be moved off page so that you can exceed the 8060 byte limit in the table definition but inserting certain combinations of values will fail. SQL Server warns you of this when creating the table:


Warning: The table "t1" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Page or row compression may be helpful if you go with the VARCHAR(27) approach. That will minimize the space used by both 0 and NULL. With VARCHAR(27) I'm able to insert 1023 0 values just fine.

StackExchange Database Administrators Q#177909, answer score: 4