NULL值在数据库中是否占用空间---MS-SQL

Question

NULL值在数据库中是否占用空间---（MS-SQL）
如果占用，和所在列的数据类型是否有关？请大虾帮忙解答！

Answer 1

曾经做过测试，100万记录 text 字段，全部是 null 的话，数据库还是很小的

Answer 2

--轉老大原話
/*
如果你是char/nchar这类定长的类型,   那不用说,   肯定占用.   
如果你是其他数据类型,   则不占用空间.   
但要注意,   即使记录中的每个列都是NULL,   记录本身也要一个地址或者指针之类,   也会有空间开销.
*/
http://topic.csdn.net/t/20060111/19/4511392.html#

Answer 3

数据是按行写入的,不会因该行某字段为NULL而把行写入的数据区域缩小,否则,更改一个NULL值将更改整个数据页的大小,这是不可思议的.

Answer 4

如果整个一行都是NULL,那恐怕就另当别论了.

Answer 5

数据部分也不占，只占用sql列的格式头中固定的几个字节。

Answer 6

csdn的blog太慢了，
http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx

Answer 7

上面表格代表一行数据，数据脚本create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀'

Answer 8

Select len(null)
--------------
null
 應該不占用吧.舉個例子吧,非null的 相當與 活人,活人要吃飯,要睡覺....
那么 null 相當與 死人, 火花后 變成一點煙灰,風一吹就沒了.

Answer 9

所有varchar列都是放在整条数据最后（二进制放在其他页），通过13、14行这样的地址指针来表示，一行数据不能跨页

Answer 10

create database db1
go
use db1
go
select top 50000 id=identity(int,0,1),col2=null,col3='abc',col4='gif' into tb1 from sysobjects a,sysobjects b
go
create database db2
go
use db2
go
select top 50000 id=identity(int,0,1),col2='abc',col3='abc',col4='gif' into tb1 from sysobjects a,sysobjects b
go
--执行完毕后,查看两数据库属性,都为大小:2.73M,可用0.94M.
/*
drop table tb1
use db1
drop table tb1
use master
drop database db1,db2
*/

Answer 11

你不要忘了数据不能跨页，一个字段的null不足以容纳另外一行记录
一页8k，一条数据6k也是要8k空间存储的，7k也是要8k空间存储的。

Answer 12

还是看我10楼图片当中的第11行吧，这个才是关键，他以位存储来标志哪些字段是null

Answer 13

如果有兴趣可以看看，如下代码，他可以在得到一行二进制内容，比如通过fn_dbLog函数或者dbcc checklog，然后检查此行内容那些是列是null实际检验这个代码是正确的。static byte[] ReadRowData()
    {
        //代码略去
        return null;
    }
    static void AnerlizeNull()
    {
        byte[] data = ReadRowData();//表示一行数据的二进制        //从第二个字节算起，data[2]
        short index = 2;
        //第二字节内容为pos_columns_count，表示第pos_columns_count个字节存放了列数信息，可以把本字节理解为指针
        short pos_columns_count = BitConverter.ToInt16(data, index);
        //pos_columns_count内容为data_columns_count，表示这个表有data_columns_count列
        short data_columns_count = BitConverter.ToInt16(data, pos_columns_count);
        //pos_columns_count的后两个字节开始表示了Null列信息，Null列信息不止占用1个字节，因为每列按位保存，如果列多，他的占用字节也多
        short pos_null_map = (short)(pos_columns_count + 2);
        //算Null信息占用多少字节
        int data_null_map_length = (int)System.Math.Ceiling((double)data_columns_count / 8);
        //读取Null列的标志内容
        byte[] data_null_map = new byte[data_null_map_length];
        System.Array.Copy(data, pos_null_map, data_null_map, 0, data_null_map_length);        //这里就可以开始检查一个表格的任何一列是否null了，null的数据是不在数据区的，只记录在Null列的标志内容中。        //比如看第10列是否null        checkNull(data_null_map, 10);
    }    static bool checkNull(byte[] map, short col_order)
    {
        int mapIndex = (col_order - 1) / 8;
        int mapExp = (col_order - 1) % 8;        int iResult = (int)System.Math.Pow(2, mapExp);        return (iResult & map[mapIndex]) != 0;
    }

Answer 14

jj,
对于定长类型(n)char与变长类型的null值存储，
它们在空间上有区别吗？

Answer 15

位置不同，比如一个表有四个字段定常1，变长2，定常3，变长4他的顺序是标志字段-定常1-定常3-标志字段\Null字典信息-变长2位置-变长4位置-变长2内容-变长4内容

Answer 16

经过实践，发现数据在mdf中的存储和ldf中完全不同..-_-|||null是占用数据空间的，我上面的解释只适用于ldf，不适用mdf，请大家谅解，十分抱歉。

Answer 17

正确的答案实践create database dbtest111
go
use dbtest111
go
create table t1(id char(10) ,name char(10))
 
insert into t1 select 'a','1'
insert into t1 select 'b','2'create table t2(id char(10) ,name char(10))
 
insert into t2 select 'a','jinjazz1'
insert into t2 select 'b',null
insert into t2 select 'c',null
insert into t2 select 'd','jinjazz2'
go
--运行此命令
dbcc ind(dbtest111,t2,0) 
--得到pagetype=1的那个行数据的pageid，比如我这里是89dbcc traceon(3604)
dbcc page(dbtest111,1,89,1) 

Answer 18

运行上面的sql语句，创建测试环境和实验脚本。得到如下结果 Slot 0, Offset 0x60, Length 27, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C06000000000:   10001800 61202020 20202020 20206a69 †....a         ji         
00000010:   6e6a617a 7a312020 0200fc†††††††††††††njazz1  ...              Slot 1, Offset 0x7b, Length 27, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C07B00000000:   10001800 62202020 20202020 20206a69 †....b         ji         
00000010:   6e6a617a 7a312020 0200fe†††††††††††††njazz1  ...              Slot 2, Offset 0x96, Length 27, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C09600000000:   10001800 63202020 20202020 20206a69 †....c         ji         
00000010:   6e6a617a 7a312020 0200fe†††††††††††††njazz1  ...              Slot 3, Offset 0xb1, Length 27, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C0B100000000:   10001800 64202020 20202020 20206a69 †....d         ji         
00000010:   6e6a617a 7a322020 0200fc†††††††††††††njazz2  ...  
每个slot都代表一行，可以看到null的不但占用空间，而且还写入了你不要的东西，最后处理是通过红色字体部分来判断那些字段是null哪些不是null，红色部分就是null_bitmap

Answer 19

我又针对varchar做了个测试，结果比较吃惊

Answer 20

创建测试环境
create database dbtest111
go
use dbtest111
go
create table t2(id char(10) ,name varchar(10))
 
insert into t2 select 'a','jinjazz1'
insert into t2 select 'b',null
insert into t2 select 'c',null
insert into t2 select 'd','jinjazz2'
go
--运行此命令
dbcc ind(dbtest111,t2,0) 
--得到pagetype=1的那个行数据的pageid，比如我这里是73

Answer 21

dbcc traceon(3604)
dbcc page(dbtest111,1,73,1) varchar比char节省地方，他的rowoffset是依次写入的，null不占用空间，空间利用很好，这个比char字段的null节省地方。
--------DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。PAGE: (1:73)
BUFFER:
BUF @0x03015944bpage = 0x0C796000                   bhash = 0x00000000                   bpageno = (1:73)
bdbid = 72                           breferences = 0                      bUse1 = 5187
bstat = 0xc0010b                     blog = 0x212121bb                    bnext = 0x00000000PAGE HEADER:
Page @0x0C796000m_pageId = (1:73)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 82     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043301888                                 
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 14                         m_slotCnt = 4                        m_freeCnt = 7996
m_freeData = 188                     m_reservedCnt = 0                    m_lsn = (30:79:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       Allocation StatusGAM (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            DATA:
Slot 0, Offset 0x60, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x5655C06000000000:   30000e00 61202020 20202020 20200200 †0...a         ..         
00000010:   fc01001d 006a696e 6a617a7a 31††††††††.....jinjazz1            Slot 1, Offset 0x7d, Length 17, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C07D00000000:   10000e00 62202020 20202020 20200200 †....b         ..         
00000010:   fe†††††††††††††††††††††††††††††††††††.                        Slot 2, Offset 0x8e, Length 17, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C08E00000000:   10000e00 63202020 20202020 20200200 †....c         ..         
00000010:   fe†††††††††††††††††††††††††††††††††††.                        Slot 3, Offset 0x9f, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x5655C09F00000000:   30000e00 64202020 20202020 20200200 †0...d         ..         
00000010:   fc01001d 006a696e 6a617a7a 32††††††††.....jinjazz2            OFFSET TABLE:Row - Offset                         
3 (0x3) - 159 (0x9f)                 
2 (0x2) - 142 (0x8e)                 
1 (0x1) - 125 (0x7d)                 
0 (0x0) - 96 (0x60)                  
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Answer 22

update t2 set name='jinjazz3' where id='b'
dbcc page(dbtest111,1,73,1) 把一条null改为非null，问题就来了。他把数据换了个地方重新写了一遍。(1 行受影响)PAGE: (1:73)
BUFFER:
BUF @0x03015944bpage = 0x0C796000                   bhash = 0x00000000                   bpageno = (1:73)
bdbid = 72                           breferences = 3                      bUse1 = 5307
bstat = 0xc0010b                     blog = 0x212121bb                    bnext = 0x00000000PAGE HEADER:
Page @0x0C796000m_pageId = (1:73)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 82     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043301888                                 
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 14                         m_slotCnt = 4                        m_freeCnt = 7984
m_freeData = 217                     m_reservedCnt = 0                    m_lsn = (30:84:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       Allocation StatusGAM (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            DATA:
Slot 0, Offset 0x60, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x5655C06000000000:   30000e00 61202020 20202020 20200200 †0...a         ..         
00000010:   fc01001d 006a696e 6a617a7a 31††††††††.....jinjazz1            Slot 1, Offset 0xbc, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x5655C0BC00000000:   30000e00 62202020 20202020 20200200 †0...b         ..         
00000010:   fc01001d 006a696e 6a617a7a 33††††††††.....jinjazz3            Slot 2, Offset 0x8e, Length 17, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x5655C08E00000000:   10000e00 63202020 20202020 20200200 †....c         ..         
00000010:   fe†††††††††††††††††††††††††††††††††††.                        Slot 3, Offset 0x9f, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x5655C09F00000000:   30000e00 64202020 20202020 20200200 †0...d         ..         
00000010:   fc01001d 006a696e 6a617a7a 32††††††††.....jinjazz2            OFFSET TABLE:Row - Offset                         
3 (0x3) - 159 (0x9f)                 
2 (0x2) - 142 (0x8e)                 
1 (0x1) - 188 (0xbc)                 
0 (0x0) - 96 (0x60)                  
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Answer 23

insert into t2 select 'g',null
update t2 set name='jinjazz44' where id='a'
dbcc page(dbtest111,1,73,1)  以后不管更新原来的还是插入新的，哪怕以前抛弃的方资源充足他也都去开辟新的战场-_-|||
当然字段从jinjazz3改成jinjinazz4倒是在原来的地方直接写入的。

(1 行受影响)PAGE: (1:73)
BUFFER:
BUF @0x03B4F330bpage = 0x4A8B8000                   bhash = 0x00000000                   bpageno = (1:73)
bdbid = 72                           breferences = 1                      bUse1 = 5651
bstat = 0xc0010b                     blog = 0x212121bb                    bnext = 0x00000000PAGE HEADER:
Page @0x4A8B8000m_pageId = (1:73)                    m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 82     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594043301888                                 
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 14                         m_slotCnt = 5                        m_freeCnt = 7964
m_freeData = 264                     m_reservedCnt = 0                    m_lsn = (30:86:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                       Allocation StatusGAM (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            DATA:
Slot 0, Offset 0xea, Length 30, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x58A1C0EA00000000:   30000e00 61202020 20202020 20200200 †0...a         ..         
00000010:   fc01001e 006a696e 6a617a7a 3434††††††.....jinjazz44           Slot 1, Offset 0xbc, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x58A1C0BC00000000:   30000e00 62202020 20202020 20200200 †0...b         ..         
00000010:   fc01001d 006a696e 6a617a7a 33††††††††.....jinjazz3            Slot 2, Offset 0x8e, Length 17, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x58A1C08E00000000:   10000e00 63202020 20202020 20200200 †....c         ..         
00000010:   fe†††††††††††††††††††††††††††††††††††.                        Slot 3, Offset 0x9f, Length 29, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x58A1C09F00000000:   30000e00 64202020 20202020 20200200 †0...d         ..         
00000010:   fc01001d 006a696e 6a617a7a 32††††††††.....jinjazz2            Slot 4, Offset 0xd9, Length 17, DumpStyle BYTERecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     
Memory Dump @0x58A1C0D900000000:   10000e00 67202020 20202020 20200200 †....g         ..         
00000010:   fe†††††††††††††††††††††††††††††††††††.                        OFFSET TABLE:Row - Offset                         
4 (0x4) - 217 (0xd9)                 
3 (0x3) - 159 (0x9f)                 
2 (0x2) - 142 (0x8e)                 
1 (0x1) - 188 (0xbc)                 
0 (0x0) - 234 (0xea)                 
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Answer 24

我觉得也应该是占用空间的，
就像C语言里打印出来的代码，其实后面还有个“/n”
这个看似没有，其实还是比原来的占用空间多一个/n的空间。

Answer 25

--------
从char和varchar的测试中可以得到以下结论，感兴趣的朋友可以测试其他类型

最后的结论是定长占用空间，不定长不占用空间。定长一旦表结构确定，每页存储记录数固定，不定长则不固定。定长在update中的性能要高于不定长（个人觉得是远远高于，但是没有测试数据不敢说话）不定长因为字段从较长望 较短的update情况下会出现差值引起的空间的浪费；而另外因为null不占空间，当null变为非null以及从短望长的update中都会出现整条数据位置变化，原来的位置空间被抛弃，而引起较大的空间浪费。

Answer 26

感谢各位的讨论和参与，特别是jinjazz大虾的鼎力支持

Answer 27

据我了解，定长字段是绝对占用的。比如DateTime为Null时也一样占用8 bytes

Answer 28

null 不占用空间在 程序中！