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,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)
执行如下：
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_BITMA |