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