Data row before and after vardecimal storage format
Paul Randal in one of his earlier BLOGs described DBCC Page paul-tells-all
and the record layout. I thought it will be interesting to show how a row looks before and after the Vardecimal storage format is enabled. So here it is
Let us take a simple table
create table t_simple (c1 char (5), c2 decimal (38,2))
insert into t_simple values ('aaaaa', 1.0)
If you run the command DBCC Page with option 3, you will get the following output
Slot 0 Offset 0x60 Length 29
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x44D0C060
00000000: 10001a00 61616161 61016400 00000000 †....aaaaa.d.....
00000010: 00000000 00000000 00000200 fc††††††††.............
Key things to note here is that row length is 29 bytes computed as follows
- Record Header = 4 bytes
- Column C1 = 5 bytes
- Null bit map and column count = 3 bytes
- Fixed length decimal value = 17 bytes
Now, let us enable Vardecimal storage format on this table. The following shows the row in the new storage format
Slot 0 Offset 0x60 Length 18
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Memory Dump @0x44E8C060
00000000: 30000900 61616161 610200fc 01001200 †0...aaaaa.......
Slot 0 Column 0 Offset 0x4 Length 5
c1 = aaaaa
Slot 0 Column 1 Offset 0x10 Length 2 [VarDecimal/VarNumeric]
c2 = 1.00
Note, now the row length is 18 bytes. So the size of the row is reduced from 29 bytes to 18 bytes representing a reduction in the size of the row of around 30%. Couple of other interesting points
- Decimal value is now stored in variable length portion of the record. The value is represented as ‘c019’, which is just 2 bytes.
- Since C2 now becomes the first variable length column, you see an overhead of 4 bytes for storing variable length column count (2 bytes) and offset array (2 bytes)