SQLServerPedia

This post is inspired by this question on MSFT Social forums. Dan Guzman posted “All fixed-length columns reserve space in the row regardless of whether or not they allow NULL”. He is absolutely right and I want to show some internal details.

USE tempdb
GO
CREATE TABLE TestINTNOTNULL (c1 INT NOT NULL);
GO
CREATE CLUSTERED INDEX CIX_TestINTNOTNULL ON TestINTNOTNULL (c1);
GO
INSERT INTO TestINTNOTNULL VALUES (1);
GO
DBCC IND ('TEMPDB', 'TestINTNOTNULL', 1);
GO
DBCC TRACEON (3604);
GO
--Change the page # based on output from DBCC IND
DBCC PAGE ('TEMPDB', 1, 187, 3);
GO

CREATE TABLE TestINTNULL (c1 INT );
GO
CREATE CLUSTERED INDEX CIX_TestINTNULL ON TestINTNULL (c1);
GO
INSERT INTO TestINTNULL VALUES (NULL);
GO
DBCC IND ('TEMPDB', 'TestINTNULL', 1);
GO
--Change the page # based on output from DBCC IND
DBCC TRACEON (3604);
GO
DBCC PAGE ('TEMPDB', 1, 186, 3);
GO

--Condensed Output

m_pageId = (1:187)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 1009   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594104053760                                
Metadata: PartitionId = 72057594099924992                                 Metadata: IndexId = 1
Metadata: ObjectId = 1389248004      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 1                       m_freeCnt = 8083
m_freeData = 107                     m_reservedCnt = 0                    m_lsn = (47:136:17)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0

m_pageId = (1:186)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 1013   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594104315904                                
Metadata: PartitionId = 72057594100187136                                 Metadata: IndexId = 1
Metadata: ObjectId = 1421248118      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 1                       m_freeCnt = 8083
m_freeData = 107                     m_reservedCnt = 0                    m_lsn = (47:216:197)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0   

m_freeCnt: Specifies the number of bytes of free space in the page. The free space available is the same irrespective of storing a null value in both pages.

Lets try a variable length column now.

CREATE TABLE TestVARCHARNOTNULL (c1 VARCHAR(10) NOT NULL);
GO
CREATE CLUSTERED INDEX CIX_TestVARCHARNOTNULL ON TestVARCHARNOTNull (c1);
GO
INSERT INTO TestVARCHARNOTNULL VALUES ('Data');
GO
DBCC IND ('TEMPDB', 'TestVARCHARNOTNULL', 1);
GO
DBCC TRACEON (3604);
GO
--Change the page # based on output from DBCC IND
DBCC PAGE ('TEMPDB', 1, 193, 3);
GO

CREATE TABLE TestVARCHARNULL (c1 VARCHAR(10) );
GO
CREATE CLUSTERED INDEX CIX_TestVARCHARNULL ON TestVARCHARNull (c1);
GO

INSERT INTO TestVARCHARNULL VALUES (NULL);
GO
DBCC IND ('TEMPDB', 'TestVARCHARNULL', 1);
GO
DBCC TRACEON (3604);
GO
--Change the page # based on output from DBCC IND
DBCC PAGE ('TEMPDB', 1, 191, 3);
GO

--Condensed Output

m_pageId = (1:193)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 1015   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594104446976                                
Metadata: PartitionId = 72057594100318208                                 Metadata: IndexId = 1
Metadata: ObjectId = 1453248232      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 1                        m_freeCnt = 8077
m_freeData = 113                     m_reservedCnt = 0                    m_lsn = (47:216:306)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

m_pageId = (1:191)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 1017   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594104578048                                
Metadata: PartitionId = 72057594100449280                                 Metadata: IndexId = 1
Metadata: ObjectId = 1469248289      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 4                          m_slotCnt = 1                       m_freeCnt = 8085
m_freeData = 105                     m_reservedCnt = 0                    m_lsn = (47:216:403)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

m_freeCnt: Specifies the number of bytes of free space in the page. The free space available varied in this case.

Is there a simpler way to test this? Ok, Lets try this below.

CREATE TABLE dbo.TestNULL (c1 INT, c2 CHAR(5000), c3 DATETIME)
GO
INSERT dbo.TestNULL SELECT NULL, NULL, NULL
GO 2

SELECT * FROM dbo.TestNULL

SELECT 
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) as SCHEMA_NAME
    ,OBJECT_NAME(i.OBJECT_ID) as TABLE_NAME
    ,COALESCE(i.name, SPACE(0)) as INDEX_NAME
    ,IN_ROW_DATA_PAGE_COUNT 
    ,i.TYPE_DESC
FROM sys.indexes i 
    INNER JOIN sys.dm_db_partition_stats ps ON i.OBJECT_ID = ps.OBJECT_ID AND i.index_id = ps.index_id 
    WHERE i.OBJECT_ID = OBJECT_ID('dbo.TestNULL')
GO

CREATE TABLE dbo.TestNOTNULL (c1 INT, c2 CHAR(5000), c3 DATETIME)
GO
INSERT dbo.TestNOTNULL SELECT 1, REPLICATE('a',5000), GETDATE()
GO 2

SELECT * FROM dbo.TestNOTNULL

SELECT 
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) as SCHEMA_NAME
    ,OBJECT_NAME(i.OBJECT_ID) as TABLE_NAME
    ,COALESCE(i.name, SPACE(0)) as INDEX_NAME
    ,IN_ROW_DATA_PAGE_COUNT 
    ,i.TYPE_DESC
FROM sys.indexes i 
    INNER JOIN sys.dm_db_partition_stats ps ON i.OBJECT_ID = ps.OBJECT_ID AND i.index_id = ps.index_id 
    WHERE i.OBJECT_ID = OBJECT_ID('dbo.TestNOTNULL')

Here is what the results show.

Acknowledgements: Paul Randal, Dan Guzman