SQLServer? BIT字段存储
Author:zfive5
Email:zfive5@163.com
引子
和同事探讨BIT怎么存储，发生了分歧
?
create? table A1
(
?? a CHAR(5),
?? b bit,
?? c CHAR(5),
?? d BIT
)
?
由于之前成天再看《存储引擎》，深知这样定义表字段也会把两个BIT压缩成1个BYTE
而是同事说会分别存2个BYTE
?
开始证明
正文
在证明前，列出一些工具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,5,1)))
??????? ) + ':' +
??????? CONVERT(VARCHAR(11),
??????????? (CONVERT(INT,4,1)) * POWER(2,24)) +
??????????? (CONVERT(INT,3,16)) +
??????????? (CONVERT(INT,2,8))? +
??????????? (CONVERT(INT,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,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),
?indexproperty(i.object_id,i.name,'minlen') as minlen,
?i.name as index_name,
?partition_id,first_page,
?a.allocation_unit_id as au_id,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)
?
?
执行如下：
insert A1(a,b,c,d) values('AAAAA','BBBBB',1)
insert A1(a,d) values('BBBBB',0,'CCCCC',0)
insert A1(a,d) values('CCCCC','DDDDD',d) values('DDDDD','FFFFF',0)
?
dbcc TRACEon(3604)
DBCC page (A,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,1)?? 03
insert A1(a,0)?? 00
insert A1(a,1)?? 02
insert A1(a,0)?? 01
?
足以证明SQLServer 不管创建的顺序，都是试图压缩到一个BYTE上去，当然超过8位，会再一次申请一个新BYTE。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
