9

I have created a table with 650 Numeric(19,4) columns. When I switch on Page Compression, by running

ALTER TABLE fct.MyTable REBUILD  WITH (DATA_COMPRESSION = PAGE);

I get

Msg 1975, Level 16, State 1
Index 'PK_Mytable' row length exceeds the maximum permissible length of '8060' bytes.

but 650 times 9 bytes is only 5850 bytes, which is quite far from the stated limit of 8060 bytes.

The server is running Windows 2012 r2 with SQL Server 2016 SP1 CU2

What is the row overhead when using Page Compression?

Here is some code to show what I mean:

/* test script to demo MSG 1975 */
DECLARE @sql NVARCHAR(max)='', @i INT =0
drop table if exists dbo.mytable;

SET @sql = 'Create table dbo.Mytable (MyTableID bigint not null 
  identity(1,1) primary key clustered, '

WHILE @i < 593 BEGIN
    SET @sql += ' Column' + LTRIM(@i) + ' numeric(19,4) null, '
    SET @i +=1
END

SET @sql += ' LastColumn int) '
--SET @sql += ' with (DATA_COMPRESSION = ROW) '
SET @sql += ' with (DATA_COMPRESSION = PAGE) '

SELECT @sql
EXEC sys.sp_executesql @sql

SELECT top 10000 * FROM dbo.MyTable MT

Row compression also fails, but at a different row count.

  • How big is your primary key? If this is a fact table and you want to compress and enhance performance, I suggest you read up on columnstore indexes, they can make quite the impact. Overhead of page compression is more cpu usage to decompress. – Stijn Wynants Apr 28 '17 at 11:51
  • @StijnWynants; 8 bytes is used for BigInts. This is indeed a fact, but there are not enough rows coming in to warrant a columnstore index. – Henrik Staun Poulsen Apr 28 '17 at 13:47
13

If you try creating your table without the clustered PK Constraint, and you'll get a slightly different error:

Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'Mytable' failed because the minimum row size would be 8067, including 1530 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

In this error message, you can see that there is 1530 bytes of internal overhead for page compression.

Now, you can do the math:

  • 8 bytes for bigint MyTableID
  • 4 bytes for int LastColumn
  • 9 bytes for each of the 593 numeric(19,4) columns (5337 bytes total)
  • 1530 bytes of compression overhead

So, 8 + 4 + (593*9) + 1530 = 6879. Wait a second.... That's still below 8060. What's up with that?!


The Page Compression algorithm actually stacks several compression algorithms together. The first step is to apply ROW compression. The overhead of row compression is not included in the 1530 bytes of overhead listed in that error message.

You can read more about how row compression works here on my blog and here in BOL. You'll note in the BOL article that it describes the numeric storage as "This storage is exactly same as the vardecimal storage format," but doesn't explain vardecimal. This post covers vardecimal a bit more--essentially, it adds 2 bytes of overhead per column to store the actual length (similar to what varchar does).

Row compression will require an additional 2 bytes for each of the 593 numeric columns, plus the bigint and int will require 1 byte of overhead each.

The row-compressed storage requirements would be:

  • 8 bytes + 1 byte overhead for bigint MyTableID
  • 4 bytes + 1 byte overhead for int LastColumn
  • 9 bytes + 2 bytes overhead for each of the 593 numeric(19,4) columns
  • 1188 bytes of ROW compression overhead

8 + 4 + (593*9) = 5349 bytes data

1 + 1 + (593*2) = 1188 bytes row compression overhead

6537 bytes total for row-compressed schema


Now that we have the row size for the row-compressed schema, we can revisit our math. The page-compressed row size will be the data size + row compression overhead + page compression overhead:

  • 8 bytes for bigint MyTableID
  • 4 bytes for int LastColumn
  • 9 bytes for each of the 593 numeric(19,4) columns
  • 1188 bytes of ROW compression overhead
  • 1530 bytes of PAGE compression overhead
  5349 bytes data 
+ 1188 bytes row compression overhead 
+ 1530 bytes page compression overhead 

8067 bytes total

  • 1
    I like your conclusion: "In most cases, you’ll find that row compression is able to save some space–but not always." 2718 bytes of overhead is a lot more than I expected. Thank you very much for taking the time to write such a detailed answer. – Henrik Staun Poulsen Apr 28 '17 at 13:43
  • 1
    @HenrikStaunPoulsen An additional important thing to remember is that SQL Server needs to assume it that your data might not be able to be compressed. So even if your data would compress to less than 8060 bytes, SQL Server must make the row size calculations based on theoretical maximum row size for uncompressable data. – AMtwo Apr 28 '17 at 13:48
  • After 3 days, I'm still amazed by to number of bytes required for Row compression; 2 bytes per column. Page Compression adds almost 3 bytes on top of that. But; thank you for your help. It was most useful. – Henrik Staun Poulsen May 1 '17 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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