Data row before and after vardecimal storage format

  • Article

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))

go

insert into t_simple values ('aaaaa', 1.0)

go

 

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

00000010: c019†††††††††††††††††††††††††††††††††..

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)