SQLSERVER中NULL位图的作用

SQLSERVER中NULL位图的作用

首先感谢宋沄剑提供的文章和sqlskill网站：www.sqlskills.com，看下面文章之前请先看一下下面两篇文章

SQL Server误区30日谈-Day6-有关NULL位图的三个误区

char nchar varchar nvarchar的区别

在SQLSERVER内部有很多地方都使用到了位图技术，包括执行计划，数据库系统页面，复制，还有这篇文章说到的数据行中的NULL位图

执行计划中有位图运算符

数据库系统页面有：DCM页面、BCM页面，详细请看：SQL Server 2008 存储结构之DCM、BCM

复制：Replication的犄角旮旯（三）--聊聊@bitmap（@bitmap 是binary类型，即二进制串；简单来说，它是用来表示所操作的字段位置的参数，通过@bitmap，分发代理从distribution.dbo.msrepl_commands中读取命令时（update操作），才会知道哪些列进行了更新；）

而这些位图技术的作用无疑都是为了 标志位

标志哪些地方发生了变化，发生了变化的就标记为1，没有发生变化的就标记为0

 

---

建立环境

建表

USE [pratice]
GO


--允许空，varchar类型
CREATE TABLE testnullvarchar(id INT ,NAME VARCHAR(20) NULL)
GO

插入数据

--插入数据
INSERT INTO [dbo].[testnullvarchar] ( [id],[Name] )
SELECT 1 ,NULL UNION ALL
SELECT 2,'你'
GO

查看

SELECT * FROM testnullvarchar

建立DBCCResult表

CREATE TABLE DBCCResult (
PageFID NVARCHAR(200),
PagePID NVARCHAR(200),
IAMFID NVARCHAR(200),
IAMPID NVARCHAR(200),
ObjectID NVARCHAR(200),
IndexID NVARCHAR(200),
PartitionNumber NVARCHAR(200),
PartitionID NVARCHAR(200),
iam_chain_type NVARCHAR(200),
PageType NVARCHAR(200),
IndexLevel NVARCHAR(200),
NextPageFID NVARCHAR(200),
NextPagePID NVARCHAR(200),
PrevPageFID NVARCHAR(200),
PrevPagePID NVARCHAR(200)
)
GO

查看数据页

--TRUNCATE TABLE DBCCResult
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnullvarchar,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 

数据页内容

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

PAGE: (1:8370)


BUFFER:


BUF @0x03CF4E64

bpage = 0x16F16000                   bhash = 0x00000000                   bpageno = (1:8370)
bdbid = 5                            breferences = 0                      bUse1 = 17294
bstat = 0x2c0000b                    blog = 0x32159bb                     bnext = 0x00000000

PAGE HEADER:


Page @0x16F16000

m_pageId = (1:8370)                  m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 521    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594072072192                                 
Metadata: PartitionId = 72057594059882496                                 Metadata: IndexId = 0
Metadata: ObjectId = 1207675350      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 2                        m_freeCnt = 8064
m_freeData = 124                     m_reservedCnt = 0                    m_lsn = (3045:22651:20)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:8088) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                      DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 11

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x0A1EC060

00000000:   10000800 01000000 0200fe†††††††††††††...........              

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               
NAME = [NULL]                        

Slot 1 Offset 0x6b Length 17

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0A1EC06B

00000000:   30000800 02000000 0200fc01 001100c4 †0...............         
00000010:   e3†††††††††††††††††††††††††††††††††††.                        

Slot 1 Column 0 Offset 0x4 Length 4

id = 2                               

Slot 1 Column 1 Offset 0xf Length 2

NAME = 你                            


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

SELECT LEN(name) FROM testnullvarchar WHERE [id]=1

---

我们看到第一行的长度是11，第二行的长度是17

 

我们看第一行记录长度11怎麽得出来的

在SQL Server页中行物理存储里对数据行的各段进行了解释

2个字节行标头存储了状态A和状态B的信息（2 bytes row header）

2个字节存储固定长度大小，因为一行记录了有varchar这些不固定长度的数据类型（2 bytes for length of fixed length columns）

SQLSERVER需要知道int、datetime、decimal这些固定长度数据类型的大小

2个字节的列数，用来存储这个表一共有多少列（2 bytes for number of columns in the table）

1个字节的null bitmap，（1 byte for null bitmap）

4个字节存储int型数据（4 bytes for int (1st column)）

2+2+2+1+4=11

我们看第二行记录长度17怎麽得出来的

2个字节行标头存储了状态A和状态B的信息（2 bytes row header）

2个字节存储固定长度大小，因为一行记录了有varchar这些不固定长度的数据类型（2 bytes for length of fixed length columns）

SQLSERVER需要知道int、datetime、decimal这些固定长度数据类型的大小

2个字节的列数，用来存储这个表一共有多少列（2 bytes for number of columns in the table）

1个字节的null bitmap，（1 byte for null bitmap）

4个字节存储int型数据（4 bytes for int (1st column)）

2个字节存储数据行中的可变长度列数量，统计数据行中一共有多少列是nvarchar ,varchar类型的列（ 2 bytes for number of variable length columns in the table）

2个字节存储可变长度偏移阵列，可变长度偏移阵列的公式

2*表格中可变长度数据类型的列数量，这个表只有一列varchar，所以2*1=2，为什麽要有可变长度偏移阵列？我估计是因为可变长度的数据类型

存储的数据是不固定的，所以要预留一些位置，当update varchar值的时候有足够的位置（2 bytes for name column offset）

2个字节存储name列的值，为什麽用两个字节大家可以看一下char nchar varchar nvarchar的区别  2 bytes for name (你)

2+2+2+1+4+2+2+2=17

---

我们这里不讨论数据占用空间的问题，这里要说的是null bitmap

无论数据行中是否有null值，都会有一个字节用来存储null bitmap

而这个null bitmap是不是总是只有一个字节的长度，他的原理是什么？？

我画了一下草图

他在每一行记录里都存在，并且标记了哪一列是NULL，哪一列不是NULL

这样在使用 len函数或者select 出表中的数据时候，先扫描NULL 位图，遇到某一位为1就跳过不select出来，从而大大加快select的速度

len函数也是一样，遇到某一位为1就返回null

SELECT LEN(name) FROM testnullvarchar WHERE [id]=1

但是一个字节只有8位，也就是只能标记表中8个列，如果一张表有10个列呢？？

我们建立另外一张表

USE [pratice]
GO


CREATE TABLE testnull10varchar(
id INT ,
NAME1 VARCHAR(2) NULL,
NAME2 VARCHAR(2) ,
NAME3 VARCHAR(2) ,
NAME4 VARCHAR(2) ,
NAME5 VARCHAR(2) ,
NAME6 VARCHAR(2) ,
NAME7 VARCHAR(2) ,
NAME8 VARCHAR(2) ,
NAME9 VARCHAR(2) ,
NAME10 VARCHAR(2) 
)
GO

--插入数据
INSERT INTO [dbo].[testnull10varchar] ( [id],[Name1],[NAME2],[NAME3],[NAME4],[NAME5],[NAME6],[NAME7],[NAME8],[NAME9],[NAME10] )
SELECT 1 ,NULL,'2','2','2','2','2','2','2','2','2' 
GO

SELECT * FROM testnull10varchar

看一下他的数据页

--TRUNCATE TABLE DBCCResult
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnull10varchar,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 


DBCC TRACEON(3604,-1)
GO
DBCC PAGE([pratice],1,8354,3)
GO

数据页内容

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

PAGE: (1:8354)


BUFFER:


BUF @0x03CF691C

bpage = 0x16FAA000                   bhash = 0x00000000                   bpageno = (1:8354)
bdbid = 5                            breferences = 0                      bUse1 = 23632
bstat = 0x2c0000b                    blog = 0x2159bbbb                    bnext = 0x00000000

PAGE HEADER:


Page @0x16FAA000

m_pageId = (1:8354)                  m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 527    m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594072465408                                 
Metadata: PartitionId = 72057594060275712                                 Metadata: IndexId = 0
Metadata: ObjectId = 1303675692      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 1                        m_freeCnt = 8051
m_freeData = 139                     m_reservedCnt = 0                    m_lsn = (3045:22809:18)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
PFS (1:8088) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                      DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x60 Length 43

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x0855C060

00000000:   30000800 01000000 0b0002f8 0a002200 †0.............".         
00000010:   23002400 25002600 27002800 29002a00 †#.$.%.&.'.(.).*.         
00000020:   2b003232 32323232 323232†††††††††††††+.222222222              

Slot 0 Column 0 Offset 0x4 Length 4

id = 1                               
NAME1 = [NULL]                       

Slot 0 Column 2 Offset 0x22 Length 1

NAME2 = 2                            

Slot 0 Column 3 Offset 0x23 Length 1

NAME3 = 2                            

Slot 0 Column 4 Offset 0x24 Length 1

NAME4 = 2                            

Slot 0 Column 5 Offset 0x25 Length 1

NAME5 = 2                            

Slot 0 Column 6 Offset 0x26 Length 1

NAME6 = 2                            

Slot 0 Column 7 Offset 0x27 Length 1

NAME7 = 2                            

Slot 0 Column 8 Offset 0x28 Length 1

NAME8 = 2                            

Slot 0 Column 9 Offset 0x29 Length 1

NAME9 = 2                            

Slot 0 Column 10 Offset 0x2a Length 1

NAME10 = 2                           


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

 

2个字节行标头存储了状态A和状态B的信息

2个字节存储固定长度大小，因为一行记录了有varchar这些不固定长度的数据类型

2个字节的列数，用来存储这个表一共有多少列

4个字节存储int型数据

2个字节存储数据行中的可变长度列数量，统计数据行中一共有多少列是nvarchar ,varchar类型的列

20个字节存储可变长度偏移阵列，可变长度偏移阵列的公式2*10=20，因为有10个varchar类型列

9个字节存储name1~name10列的值，因为name1值为null，所以实际字节长度为9个字节即统计name2~name10的值，详细可以再看一下

char nchar varchar nvarchar的区别

2个字节的null bitmap

2+2+2+4+2+20+9+2=43

下面用草图演示

 

 所以这里NULL位图的长度是根据你当前表中有多少列来决定的

testnull10varchar表有11个列相当于需要2个字节的NULL 位图了

---

关于行记录属性

在上面的实验中testnullvarchar表第一行记录和第二行记录的行记录属性都不一样，实际上这个行记录属性只是指明

testnullvarchar表第二行记录有可变长度类型数据，并且不为NULL，这并不意味着SQLSERVER扫描到第一行记录的时候，

当发现record attributes（行记录属性）=NULL_BITMAP 就跳过第一行，不去读取第一行记录里的数据

扫描到第二行记录的时候，当发现record attributes（行记录属性）=NULL_BITMAP VARIABLE_COLUMNS知道第二行记录有

可变长度数据并且不为NULL就去读取第二行记录里的数据

 

论坛里的rmiao大侠给出了下面解释：

Null_bitmap in record attributes means this record contains null_bitmap field.

Likewise,'null_bitmap variable_columns' means this record contains null_bitmap field with variable length columns.

 

不管数据行中是否有NULL值，建表的时候是否允许NULL，数据页中的行都会有record attributes = NULL_BITMAP

而record attributes =NULL_BITMAP VARIABLE_COLUMNS 只是说明了数据行有可变长度数据类型并且不为NULL

所以SQLSERVER在任何情况下都会去扫描这个NULL 位图的，除了Record Attributes = No null bitmap

详细可以看一下SQL Server误区30日谈-Day6-有关NULL位图的三个误区

 

---

证明

建立测试环境

USE [pratice]
GO


--允许空，char类型
CREATE TABLE testnullchar(id INT,NAME CHAR(20) NULL)
GO
--不允许空，varchar类型
CREATE TABLE testnotnullvarchar(id INT ,NAME VARCHAR(20) NOT NULL)
GO
--不允许空，char类型
CREATE TABLE testnotnullchar(id INT ,NAME CHAR(20) NOT NULL)
GO

INSERT INTO [dbo].[testnullchar] ( [id],[Name] )
SELECT 1,NULL UNION ALL
SELECT 2,'你'
GO

INSERT INTO [dbo].[testnotnullchar] ( [id],[NAME] )
SELECT 1,'' UNION ALL
SELECT 2,'你'
GO

INSERT INTO [dbo].[testnotnullvarchar] ( [id],[NAME] )
SELECT 1,'' UNION ALL
SELECT 2,'你'
GO

SELECT * FROM testnullchar
SELECT * FROM testnotnullchar
SELECT * FROM testnotnullvarchar

查看数据页

------------------------------------------------------------------------
--TRUNCATE TABLE DBCCResult
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnullchar,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 


DBCC TRACEON(3604,-1)
GO
DBCC PAGE([pratice],1,15658,3)
GO


--------------------------------------------------------
--TRUNCATE TABLE DBCCResult
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnotnullvarchar,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 


DBCC TRACEON(3604,-1)
GO
DBCC PAGE([pratice],1,8353,3)
GO


--------------------------------------------------------
--TRUNCATE TABLE DBCCResult
INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnotnullchar,-1) ')

SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 


DBCC TRACEON(3604,-1)
GO
DBCC PAGE([pratice],1,37266,3)
GO



----------------------------------------------------------------

testnullchar表，因为testnullchar表没有可变长度数据类型，所以两行数据都是NULL_BITMAP

 

testnotnullvarchar表，因为testnotnullvarchar表有可变长度数据类型，所以第二行为NULL_BITMAP VARIABLE_COLUMNS

testnotnullchar表，跟testnullchar表一样

 

 

而NULL_BITMAP VARIABLE_COLUMNS只是说明了数据行中有可变长度类型的数据，不是说某个字段就是可变长度数据类型

---

题外话

其实这篇文章是我前天看到某篇文章特别而写的，觉得这个null bitmap要好好研究一下，以免被人误导

本人不喜欢某些人以泰山压顶之势去评论别人，你知道的某些东西可能不一定正确的，而别人不知道的东西，日后一定会知道的，只是时间问题

知道某样东西的时间问题，迟早问题，或者这就是技术人的通病，自己技术厉害了，就XXXXXX！！！

 

---

如有不对的地方，欢迎大家拍砖o(∩_∩)o 哈哈