Data row before and after vardecimal storage format

Data row before and after vardecimal storage format

  • Comments 2

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)
Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • SQL Server 2005 SP2 では、decimal, numeric データ型利用時のディスクスペースが減る !? vardecimal とは.

  • 你好,看了您写的内容,vardecimal 存储格式。

    当 c2 = 1.00 存储是 0xc019

    c0我已经知道 代表的 意思了

    请问 19 怎么 解读,才能得到1呢?

Page 1 of 1 (2 items)