SQLServer BIT字段存储

SQLServer  BIT字段存储

Author:zfive5

Email:zfive5@163.com

引子

和同事探讨BIT怎么存储,发生了分歧

 

create  table A1

(

   a CHAR(5),

   b bit,

   c CHAR(5),

   d BIT

)

 

由于之前成天再看《存储引擎》,深知这样定义表字段也会把两个BIT压缩成1BYTE

而是同事说会分别存2BYTE

 

开始证明

正文

在证明前,列出一些工具SQL:

CREATE FUNCTION convert_page_nums(@page_num binary(6))

RETURNS VARCHAR(11)

AS

BEGIN

    RETURN(

        CONVERT(VARCHAR(2),(

                CONVERT(INT,SUBSTRING(@page_num,6,1))* POWER(2,8)

            ) +

            (CONVERT(INT,SUBSTRING(@page_num,5,1)))

        ) + ‘:‘ +

        CONVERT(VARCHAR(11),

            (CONVERT(INT,SUBSTRING(@page_num,4,1)) * POWER(2,24)) +

            (CONVERT(INT,SUBSTRING(@page_num,3,1)) * POWER(2,16)) +

            (CONVERT(INT,SUBSTRING(@page_num,2,1)) * POWER(2,8))  +

            (CONVERT(INT,SUBSTRING(@page_num,1,1)))

        )

    )

END

 

 

select object_name(object_id) as name,partition_id,partition_number as pnum,rows,

 allocation_unit_id as au_id,type_desc as page_type_desc,total_pages as pages

from sys.partitions p join sys.allocation_units a

 on p.partition_id = a.container_id

where object_id = object_id(‘dbo.A1‘)

 

select convert(char(8),object_name(i.object_id)) as table_name,

 i.name as index_name,i.index_id,i.type_desc as index_type,

 partition_id,partition_number as pnum,rows,

 allocation_unit_id as au_id,a.type_desc as page_type_desc,total_pages as pages

from sys.indexes i join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id

 join sys.allocation_units a on p.partition_id = a.container_id

where i.object_id = object_id(‘dbo.a1‘)

 

 

执行如下SQL:

select convert(char(8),object_name(i.object_id)) as table_name,

 indexproperty(i.object_id,i.name,‘minlen‘) as minlen,

 i.name as index_name,i.index_id,i.type_desc as index_type,

 partition_id,partition_number as pnum,first_page,rows,

 a.allocation_unit_id as au_id,a.type_desc as page_type_desc,a.total_pages as pages

from sys.indexes i join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id

 join sys.system_internals_allocation_units a on p.partition_id = a.container_id

where i.object_id = object_id(‘dbo.a1‘)

 

SELECT DBO.convert_page_nums(0xEC2D00000100)

 SQLServer  BIT字段存储

 

执行如下:

insert A1(a,b,c,d) values(‘AAAAA‘,1,‘BBBBB‘,1)

insert A1(a,b,c,d) values(‘BBBBB‘,0,‘CCCCC‘,0)

insert A1(a,b,c,d) values(‘CCCCC‘,0,‘DDDDD‘,1)

insert A1(a,b,c,d) values(‘DDDDD‘,1,‘FFFFF‘,0)

 

dbcc TRACEon(3604)

DBCC page (A,1,121,3)

 

得到如下信息:

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

 

PAGE: (1:121)

 

 

BUFFER:

 

 

BUF @0x000000046E165B80

 

bpage = 0x000000045DDDA000          bhash = 0x0000000000000000          bpageno = (1:121)

bdbid = 9                           breferences = 0                     bcputicks = 0

bsampleCount = 0                    bUse1 = 3353                        bstat = 0x10b

blog = 0x15acc                      bnext = 0x0000000000000000         

 

PAGE HEADER:

 

 

Page @0x000000045DDDA000

 

m_pageId = (1:121)                  m_headerVersion = 1                 m_type = 1

m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 85    m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594043498496                               

Metadata: PartitionId = 72057594039107584                                Metadata: IndexId = 0

Metadata: ObjectId = 581577110      m_prevPage = (0:0)                  m_nextPage = (0:0)

pminlen = 15                        m_slotCnt = 4                       m_freeCnt = 8016

m_freeData = 168                    m_reservedCnt = 0                   m_lsn = (34:25:2)

m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0

m_tornBits = 0                      DB Frag ID = 1                     

 

Allocation Status

 

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED             

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED          

 

Slot 0 Offset 0x60 Length 18

 

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 18

 

Memory Dump @0x0000000013D6A060

 

0000000000000000:   10000f00 41414141 41034242 42424204 0000      ....AAAAA.BBBBB...

 

Slot 0 Column 1 Offset 0x4 Length 5 Length (physical) 5

 

a = AAAAA                          

 

Slot 0 Column 2 Offset 0x9 Length 1 (Bit position 0)

 

b = 1                              

 

Slot 0 Column 3 Offset 0xa Length 5 Length (physical) 5

 

c = BBBBB                          

 

Slot 0 Column 4 Offset 0x9 Length 1 (Bit position 1)

 

d = 1                               

 

Slot 1 Offset 0x72 Length 18

 

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 18

 

Memory Dump @0x0000000013D6A072

 

0000000000000000:   10000f00 42424242 42004343 43434304 0000      ....BBBBB.CCCCC...

 

Slot 1 Column 1 Offset 0x4 Length 5 Length (physical) 5

 

a = BBBBB                          

 

Slot 1 Column 2 Offset 0x9 Length 1 (Bit position 0)

 

b = 0                              

 

Slot 1 Column 3 Offset 0xa Length 5 Length (physical) 5

 

c = CCCCC                          

 

Slot 1 Column 4 Offset 0x9 Length 1 (Bit position 1)

 

d = 0                              

 

Slot 2 Offset 0x84 Length 18

 

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 18

 

Memory Dump @0x0000000013D6A084

 

0000000000000000:   10000f00 43434343 43024444 44444404 0000      ....CCCCC.DDDDD...

 

Slot 2 Column 1 Offset 0x4 Length 5 Length (physical) 5

 

a = CCCCC                          

 

Slot 2 Column 2 Offset 0x9 Length 1 (Bit position 0)

 

b = 0                              

 

Slot 2 Column 3 Offset 0xa Length 5 Length (physical) 5

 

c = DDDDD                          

 

Slot 2 Column 4 Offset 0x9 Length 1 (Bit position 1)

 

d = 1                              

 

Slot 3 Offset 0x96 Length 18

 

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 18

 

Memory Dump @0x0000000013D6A096

 

0000000000000000:   10000f00 44444444 44014646 46464604 0000      ....DDDDD.FFFFF...

 

Slot 3 Column 1 Offset 0x4 Length 5 Length (physical) 5

 

a = DDDDD                          

 

Slot 3 Column 2 Offset 0x9 Length 1 (Bit position 0)

 

b = 1                              

 

Slot 3 Column 3 Offset 0xa Length 5 Length (physical) 5

 

c = FFFFF                           

 

Slot 3 Column 4 Offset 0x9 Length 1 (Bit position 1)

 

d = 0                              

 

 

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

 

 

 

 

更直观的比较

 

1、10000f00 41414141 41034242 42424204 0000 

2、10000f00 42424242 42004343 43434304 0000

3、10000f00 44444444 44014646 46464604 0000

4、10000f00 44444444 44014646 46464604 0000

 

二进制的中间分别为 03 00  02  01

insert A1(a,b,c,d) values(‘AAAAA‘,1,‘BBBBB‘,1)   03

insert A1(a,b,c,d) values(‘BBBBB‘,0,‘CCCCC‘,0)   00

insert A1(a,b,c,d) values(‘CCCCC‘,0,‘DDDDD‘,1)   02

insert A1(a,b,c,d) values(‘DDDDD‘,1,‘FFFFF‘,0)   01

 

足以证明SQLServer 不管创建的顺序,都是试图压缩到一个BYTE上去,当然超过8位,会再一次申请一个新BYTE。

 

SQLServer BIT字段存储,布布扣,bubuko.com

SQLServer BIT字段存储

上一篇:相克军_Oracle体系_随堂笔记016-参数文件及数据库的启动和关闭


下一篇:Mac查看关闭MongoDB