SQLSERVER中NULL位图的作用

时间:2022-09-11 21:41:32

SQLSERVER中NULL位图的作用

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

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

char nchar varchar nvarchar的区别

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

执行计划中有位图运算符

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

SQLSERVER中NULL位图的作用

建立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

SQLSERVER中NULL位图的作用


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

SQLSERVER中NULL位图的作用

SQLSERVER中NULL位图的作用

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

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

SQLSERVER中NULL位图的作用

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

SQLSERVER中NULL位图的作用

我们看第二行记录长度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个字节存储可变长度偏移阵列,可变长度偏移阵列的公式SQLSERVER中NULL位图的作用

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是不是总是只有一个字节的长度,他的原理是什么??

我画了一下草图

SQLSERVER中NULL位图的作用

SQLSERVER中NULL位图的作用

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

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

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

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

SQLSERVER中NULL位图的作用

但是一个字节只有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,'','','','','','','','',''
GO SELECT * FROM testnull10varchar

SQLSERVER中NULL位图的作用

看一下他的数据页

 --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 输出了错误信息,请与系统管理员联系。

SQLSERVER中NULL位图的作用

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

下面用草图演示

SQLSERVER中NULL位图的作用

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

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

SQLSERVER中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

SQLSERVER中NULL位图的作用

查看数据页

 ------------------------------------------------------------------------
--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

SQLSERVER中NULL位图的作用

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

SQLSERVER中NULL位图的作用

testnotnullchar表,跟testnullchar表一样

SQLSERVER中NULL位图的作用

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

SQLSERVER中NULL位图的作用


题外话

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

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

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

SQLSERVER中NULL位图的作用

SQLSERVER中NULL位图的作用


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

SQLSERVER中NULL位图的作用的更多相关文章

  1. NUll在oracle与sqlserver中使用相同与区别

    最近在使用Oracle进行开发,遇到很多与以前使用sqlserver的不同语法.今天遇到null在两种数据库上面操作上的差别,在此记录两种数据库上的差异. null 与字符串相加 1.在oracle中 ...

  2. SQLSERVER中KeyHashValue的作用(下)

    SQLSERVER中KeyHashValue的作用(下) 昨天中午跟高文佳童鞋讨论了KeyHashValue的作用,到最后还是没有讨论出结果 昨天晚上德国的兄弟傅文伟做了一下实验,将实验结果交给我 感 ...

  3. SQLSERVER中KeyHashValue的作用(上)

    SQLSERVER中KeyHashValue的作用(上) SQLSERVER中KeyHashValue的作用(下) 原文的标题是:SQLSERVER在索引下如何找到哈希值的随想 现在知道KeyHash ...

  4. JS中NULL和undifined区别及NULL的作用

    1.博客地址:http://www.cnblogs.com/eastday/archive/2010/03/03/1677324.html 2.参考地址2:https://www.zhihu.com/ ...

  5. SQLSERVER中的假脱机spool

    SQLSERVER中的假脱机spool 我发现网上对于假脱机的解释都非常零散,究竟假脱机是什么? 这几天在家里研究了一下,收集了很多网上的资料 假脱机是中文的翻译,而英文的名字叫做 spool 在徐老 ...

  6. 我是如何在SQLServer中处理每天四亿三千万记录的

    首先声明,我只是个程序员,不是专业的DBA,以下这篇文章是从一个问题的解决过程去写的,而不是一开始就给大家一个正确的结果,如果文中有不对的地方,请各位数据库大牛给予指正,以便我能够更好的处理此次业务. ...

  7. Sqlserver中一直在用又经常被忽略的知识点一

    已经有快2个月没有更新博客了,实在是因为最近发生了太多的事情,辞了工作,在湘雅医院待了一个多月,然后又新换了工作...... 在平时的工作中,Sqlserver中许多知识点是经常用到的,但是有时候我们 ...

  8. 再谈SQL Server中日志的的作用

    简介     之前我已经写了一个关于SQL Server日志的简单系列文章.本篇文章会进一步挖掘日志背后的一些概念,原理以及作用.如果您没有看过我之前的文章,请参阅:     浅谈SQL Server ...

  9. 【转】我是如何在SQLServer中处理每天四亿三千万记录的

    原文转自:http://blog.jobbole.com/80395/ 首先声明,我只是个程序员,不是专业的DBA,以下这篇文章是从一个问题的解决过程去写的,而不是一开始就给大家一个正确的结果,如果文 ...

随机推荐

  1. Cordova从服务器更新客户端的JS文件

    思路: 1.主要使用 Cordova的File插件 2.获取需要替换的js文件安装后的路径 3.软件使用js发起ajax请求,后台返回版本号跟客户端版本号对比 4.如果发现需要更新js文件,则用js调 ...

  2. jquery里用each遍历的值存到数组和字符串

    $("img").each(function(){ var a = $(this).attr("src"); }); //遍历后存放到数组中..要用的时候再根据 ...

  3. Chrome 将默认不播放非重要 Flash 内容

    Chrome 45将不再自动播放Flash,可能是45以后的版本都不自动播放了,没有具体测试. 小尺寸flash不被chrome播放,需要手动点击才能播放如何解决: <p>1.同域名fla ...

  4. cf492C Vanya and Exams

    C. Vanya and Exams time limit per test 1 second memory limit per test 256 megabytes input standard i ...

  5. Cookie已经过时,细看Facebook&comma; Google&comma; Apple如何追踪用户

    http://www.infoq.com/cn/news/2014/10/cookie-facebook-google-apple 链接地址 Cookie,有时也用其复数形式Cookies,指某些网站 ...

  6. vi命令加行号查找替换等命令

    一.加行号           : set nu二.vi查找:    当你用vi打开一个文件后,因为文件太长,如何才能找到你所要查找的关键字呢?在vi里可没有菜单-〉查找,              ...

  7. Docker发布程序那些事

    最近使用docker比较频繁,所以也难免碰到一些坑,在这里记录一下,给自己写个笔记 1.docker 1.13.0版本不支持docker stack deploy -c 多个 -c的yml文件合并 如 ...

  8. JavaScript流程控制及函数

    1 流程控制 1.1 条件语句 分支结构 单向分支 if (条件表达式) { code...} 双向分支 if (条件表达式){    } else {    } <!DOCTYPE html& ...

  9. W3School学习网站

    http://www.w3school.com.cn/tags/att_form_autocomplete.asp

  10. PLSQL Developer连接远程Oracle方法(非安装client)

    远程连接Oracle比較麻烦,通常须要安装oracle的客户端才干实现. 通过instantclient能够比較简单的连接远程的Oracle. 1.新建文件夹D:\Oracle_Cleint用于存放相 ...