您所在的位置：程序员俱乐部 > 编程开发 > .NET > SQLSERVER中NULL位图的作用

SQLSERVER中NULL位图的作用

2013/9/15 10:46:20 桦仔博客园我要评论(0)

摘要：SQLSERVER中NULL位图的作用首先感谢宋沄剑提供的文章和sqlskill网站：www.sqlskills.com，看下面文章之前请先看一下下面两篇文章SQLServer误区30日谈-Day6-有关NULL位图的三个误区charncharvarcharnvarchar的区别在SQLSERVER内部有很多地方都使用到了位图技术，包括执行计划，数据库系统页面，还有这篇文章说到的数据行中的NULL位图执行计划中有位图运算符数据库系统页面有：DCM页面、BCM页面，详细请看
标签：Server SQLSERVER SQL

SQLSERVER中NULL位图的作用

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

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

char nchar varchar nvarchar的区别

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

执行计划中有位图运算符

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

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

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

建立环境

建表

1 USE [pratice]
2 GO
3 
4 
5 --允许空，varchar类型
6 CREATE TABLE testnullvarchar(id INT ,NAME VARCHAR(20) NULL)
7 GO

插入数据

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

查看

1 SELECT * FROM testnullvarchar

建立DBCCResult表

class="code_img_closed" src="/Upload/Images/2013091510/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('12ecb5aa-de7d-46d7-9975-9f95b20ebb7d',event)" src="/Upload/Images/2013091510/2B1B950FA3DF188F.gif" alt="" />

 1 CREATE TABLE DBCCResult (
 2 PageFID NVARCHAR(200),
 3 PagePID NVARCHAR(200),
 4 IAMFID NVARCHAR(200),
 5 IAMPID NVARCHAR(200),
 6 ObjectID NVARCHAR(200),
 7 IndexID NVARCHAR(200),
 8 PartitionNumber NVARCHAR(200),
 9 PartitionID NVARCHAR(200),
10 iam_chain_type NVARCHAR(200),
11 PageType NVARCHAR(200),
12 IndexLevel NVARCHAR(200),
13 NextPageFID NVARCHAR(200),
14 NextPagePID NVARCHAR(200),
15 PrevPageFID NVARCHAR(200),
16 PrevPagePID NVARCHAR(200)
17 )
18 GO

View Code

查看数据页

1 --TRUNCATE TABLE DBCCResult
2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnullvarchar,-1) ')
3 
4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

View Code

数据页内容

 1 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
 2 
 3 PAGE: (1:8370)
 4 
 5 
 6 BUFFER:
 7 
 8 
 9 BUF @0x03CF4E64
10 
11 bpage = 0x16F16000                   bhash = 0x00000000                   bpageno = (1:8370)
12 bdbid = 5                            breferences = 0                      bUse1 = 17294
13 bstat = 0x2c0000b                    blog = 0x32159bb                     bnext = 0x00000000
14 
15 PAGE HEADER:
16 
17 
18 Page @0x16F16000
19 
20 m_pageId = (1:8370)                  m_headerVersion = 1                  m_type = 1
21 m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
22 m_objId (AllocUnitId.idObj) = 521    m_indexId (AllocUnitId.idInd) = 256  
23 Metadata: AllocUnitId = 72057594072072192                                 
24 Metadata: PartitionId = 72057594059882496                                 Metadata: IndexId = 0
25 Metadata: ObjectId = 1207675350      m_prevPage = (0:0)                   m_nextPage = (0:0)
26 pminlen = 8                          m_slotCnt = 2                        m_freeCnt = 8064
27 m_freeData = 124                     m_reservedCnt = 0                    m_lsn = (3045:22651:20)
28 m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
29 m_tornBits = 0                       
30 
31 Allocation Status
32 
33 GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
34 PFS (1:8088) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                      DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED            
36 
37 Slot 0 Offset 0x60 Length 11
38 
39 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
40 Memory Dump @0x0A1EC060
41 
42 00000000:   10000800 01000000 0200fe†††††††††††††...........              
43 
44 Slot 0 Column 0 Offset 0x4 Length 4
45 
46 id = 1                               
47 NAME = [NULL]                        
48 
49 Slot 1 Offset 0x6b Length 17
50 
51 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
52 
53 Memory Dump @0x0A1EC06B
54 
55 00000000:   30000800 02000000 0200fc01 001100c4 †0...............         
56 00000010:   e3†††††††††††††††††††††††††††††††††††.                        
57 
58 Slot 1 Column 0 Offset 0x4 Length 4
59 
60 id = 2                               
61 
62 Slot 1 Column 1 Offset 0xf Length 2
63 
64 NAME = 你                            
65 
66 
67 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

View Code

1 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

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

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

我们建立另外一张表

 1 USE [pratice]
 2 GO
 3 
 4 
 5 CREATE TABLE testnull10varchar(
 6 id INT ,
 7 NAME1 VARCHAR(2) NULL,
 8 NAME2 VARCHAR(2) ,
 9 NAME3 VARCHAR(2) ,
10 NAME4 VARCHAR(2) ,
11 NAME5 VARCHAR(2) ,
12 NAME6 VARCHAR(2) ,
13 NAME7 VARCHAR(2) ,
14 NAME8 VARCHAR(2) ,
15 NAME9 VARCHAR(2) ,
16 NAME10 VARCHAR(2) 
17 )
18 GO
19 
20 --插入数据
21 INSERT INTO [dbo].[testnull10varchar] ( [id],[Name1],[NAME2],[NAME3],[NAME4],[NAME5],[NAME6],[NAME7],[NAME8],[NAME9],[NAME10] )
22 SELECT 1 ,NULL,'2','2','2','2','2','2','2','2','2' 
23 GO
24 
25 SELECT * FROM testnull10varchar

View Code

看一下他的数据页

 1 --TRUNCATE TABLE DBCCResult
 2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnull10varchar,-1) ')
 3 
 4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
 5 
 6 
 7 DBCC TRACEON(3604,-1)
 8 GO
 9 DBCC PAGE([pratice],1,8354,3)
10 GO

View Code

数据页内容

 1 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
 2 
 3 PAGE: (1:8354)
 4 
 5 
 6 BUFFER:
 7 
 8 
 9 BUF @0x03CF691C
10 
11 bpage = 0x16FAA000                   bhash = 0x00000000                   bpageno = (1:8354)
12 bdbid = 5                            breferences = 0                      bUse1 = 23632
13 bstat = 0x2c0000b                    blog = 0x2159bbbb                    bnext = 0x00000000
14 
15 PAGE HEADER:
16 
17 
18 Page @0x16FAA000
19 
20 m_pageId = (1:8354)                  m_headerVersion = 1                  m_type = 1
21 m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
22 m_objId (AllocUnitId.idObj) = 527    m_indexId (AllocUnitId.idInd) = 256  
23 Metadata: AllocUnitId = 72057594072465408                                 
24 Metadata: PartitionId = 72057594060275712                                 Metadata: IndexId = 0
25 Metadata: ObjectId = 1303675692      m_prevPage = (0:0)                   m_nextPage = (0:0)
26 pminlen = 8                          m_slotCnt = 1                        m_freeCnt = 8051
27 m_freeData = 139                     m_reservedCnt = 0                    m_lsn = (3045:22809:18)
28 m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
29 m_tornBits = 0                       
30 
31 Allocation Status
32 
33 GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               
34 PFS (1:8088) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                      DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED            
36 
37 Slot 0 Offset 0x60 Length 43
38 
39 Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
40 
41 Memory Dump @0x0855C060
42 
43 00000000:   30000800 01000000 0b0002f8 0a002200 †0.............".         
44 00000010:   23002400 25002600 27002800 29002a00 †#.$.%.&.'.(.).*.         
45 00000020:   2b003232 32323232 323232†††††††††††††+.222222222              
46 
47 Slot 0 Column 0 Offset 0x4 Length 4
48 
49 id = 1                               
50 NAME1 = [NULL]                       
51 
52 Slot 0 Column 2 Offset 0x22 Length 1
53 
54 NAME2 = 2                            
55 
56 Slot 0 Column 3 Offset 0x23 Length 1
57 
58 NAME3 = 2                            
59 
60 Slot 0 Column 4 Offset 0x24 Length 1
61 
62 NAME4 = 2                            
63 
64 Slot 0 Column 5 Offset 0x25 Length 1
65 
66 NAME5 = 2                            
67 
68 Slot 0 Column 6 Offset 0x26 Length 1
69 
70 NAME6 = 2                            
71 
72 Slot 0 Column 7 Offset 0x27 Length 1
73 
74 NAME7 = 2                            
75 
76 Slot 0 Column 8 Offset 0x28 Length 1
77 
78 NAME8 = 2                            
79 
80 Slot 0 Column 9 Offset 0x29 Length 1
81 
82 NAME9 = 2                            
83 
84 Slot 0 Column 10 Offset 0x2a Length 1
85 
86 NAME10 = 2                           
87 
88 
89 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

View Code

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位图的三个误区

证明

建立测试环境

 1 USE [pratice]
 2 GO
 3 
 4 
 5 --允许空，char类型
 6 CREATE TABLE testnullchar(id INT,NAME CHAR(20) NULL)
 7 GO
 8 --不允许空，varchar类型
 9 CREATE TABLE testnotnullvarchar(id INT ,NAME VARCHAR(20) NOT NULL)
10 GO
11 --不允许空，char类型
12 CREATE TABLE testnotnullchar(id INT ,NAME CHAR(20) NOT NULL)
13 GO
14 
15 INSERT INTO [dbo].[testnullchar] ( [id],[Name] )
16 SELECT 1,NULL UNION ALL
17 SELECT 2,'你'
18 GO
19 
20 INSERT INTO [dbo].[testnotnullchar] ( [id],[NAME] )
21 SELECT 1,'' UNION ALL
22 SELECT 2,'你'
23 GO
24 
25 INSERT INTO [dbo].[testnotnullvarchar] ( [id],[NAME] )
26 SELECT 1,'' UNION ALL
27 SELECT 2,'你'
28 GO
29 
30 SELECT * FROM testnullchar
31 SELECT * FROM testnotnullchar
32 SELECT * FROM testnotnullvarchar

View Code

查看数据页

 1 ------------------------------------------------------------------------
 2 --TRUNCATE TABLE DBCCResult
 3 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnullchar,-1) ')
 4 
 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
 6 
 7 
 8 DBCC TRACEON(3604,-1)
 9 GO
10 DBCC PAGE([pratice],1,15658,3)
11 GO
12 
13 
14 --------------------------------------------------------
15 --TRUNCATE TABLE DBCCResult
16 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnotnullvarchar,-1) ')
17 
18 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
19 
20 
21 DBCC TRACEON(3604,-1)
22 GO
23 DBCC PAGE([pratice],1,8353,3)
24 GO
25 
26 
27 --------------------------------------------------------
28 --TRUNCATE TABLE DBCCResult
29 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,testnotnullchar,-1) ')
30 
31 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 
32 
33 
34 DBCC TRACEON(3604,-1)
35 GO
36 DBCC PAGE([pratice],1,37266,3)
37 GO
38 
39 
40 
41 ----------------------------------------------------------------