NULL values consume storage in SQL Server Tables?

While we were discussing on data types to be used and null-able columns, a question was raised, asking whether the space is used for NULLs as SQL Server uses for other data types. My answer was Yes and No because it depends on the data type we have used.

NULL is not exactly a value. It indicates that the value is unknown hence it requires some bits/bytes to maintain it. However, if I set one of my columns value as NULL, can I assume that it will not use the space that data type suppose to use? For example, if I have a column with data type int that uses 4 bytes per value and I inserted a record with NULL for the column, will SQL Server still uses 4 bytes or few bits for the NULL?

It is always better to write some codes for testing and come to a conclusion. Therefore, let's test this with four tables. The below code creates;

1. Customer_Without_NullValues_FixedWidthType table
2. Customer_With_NullValues_FixedWidthType table
3. Customer_Without_NullValues_VaryWidthType table
4. Customer_With_NullValues_VaryWidthType table

The last three columns of First and Second tables are set with date data type and all are null-able. And last three columns of Third and Forth tables are set with varchar(4000) data type and all are null-able.

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. CREATE TABLE dbo.Customer_Without_NullValues_FixedWidthType  
5. (  
6.  Id int identity(1,1) PRIMARY KEY  
7.  , Name varchar(100) NOT NULL  
8.  , DateOfBirth date NULL  
9.  , RegisteredDate date NULL  
10.  , LastPurchasedDate date NULL  
11. );  
12. GO  
13.   
14. CREATE TABLE dbo.Customer_With_NullValues_FixedWidthType  
15. (  
16.  Id int identity(1,1) PRIMARY KEY  
17.  , Name varchar(100) NOT NULL  
18.  , DateOfBirth date NULL  
19.  , RegisteredDate date NULL  
20.  , LastPurchasedDate date NULL  
21. );  
22. GO  
23.   
24.   
25. CREATE TABLE dbo.Customer_Without_NullValues_VaryWidthType  
26. (  
27.  Id int identity(1,1) PRIMARY KEY  
28.  , Name varchar(100) NOT NULL  
29.  , Details1 varchar(4000) NULL  
30.  , Details2 varchar(4000) NULL  
31.  , Details3 varchar(4000)  NULL  
32. );  
33. GO  
34.   
35. CREATE TABLE dbo.Customer_With_NullValues_VaryWidthType  
36. (  
37.  Id int identity(1,1) PRIMARY KEY  
38.  , Name varchar(100) NOT NULL  
39.  , Details1 varchar(4000) NULL  
40.  , Details2 varchar(4000) NULL  
41.  , Details3 varchar(4000)  NULL  
42. );  
43. GO  

USE tempdb;
GO

CREATE TABLE dbo.Customer_Without_NullValues_FixedWidthType
(
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , DateOfBirth date NULL
 , RegisteredDate date NULL
 , LastPurchasedDate date NULL
);
GO

CREATE TABLE dbo.Customer_With_NullValues_FixedWidthType
(
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , DateOfBirth date NULL
 , RegisteredDate date NULL
 , LastPurchasedDate date NULL
);
GO


CREATE TABLE dbo.Customer_Without_NullValues_VaryWidthType
(
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , Details1 varchar(4000) NULL
 , Details2 varchar(4000) NULL
 , Details3 varchar(4000)  NULL
);
GO

CREATE TABLE dbo.Customer_With_NullValues_VaryWidthType
(
 Id int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , Details1 varchar(4000) NULL
 , Details2 varchar(4000) NULL
 , Details3 varchar(4000)  NULL
);
GO

Next code insert 100,000 records for each table. However, last three columns of Second and Forth tables are filled with NULLs instead of known values.

view plainprint?

1. INSERT INTO dbo.Customer_Without_NullValues_FixedWidthType  
2.  (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)  
3.  VALUES  
4.  ('a', getdate(), getdate(), getdate());  
5.   
6. INSERT INTO dbo.Customer_With_NullValues_FixedWidthType  
7.  (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)  
8.  VALUES  
9.  ('a', null, null, null);  
10.   
11. INSERT INTO dbo.Customer_Without_NullValues_VaryWidthType  
12.  (Name, Details1, Details2, Details3)  
13.  VALUES  
14.  ('a', REPLICATE('a', 4000), REPLICATE('a', 4000), REPLICATE('a', 4000));  
15.   
16. INSERT INTO dbo.Customer_With_NullValues_VaryWidthType  
17.  (Name, Details1, Details2, Details3)  
18.  VALUES  
19.  ('a', null, null, null);  
20.   
21. GO 100000  

INSERT INTO dbo.Customer_Without_NullValues_FixedWidthType
 (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)
 VALUES
 ('a', getdate(), getdate(), getdate());

INSERT INTO dbo.Customer_With_NullValues_FixedWidthType
 (Name, DateOfBirth, RegisteredDate, LastPurchasedDate)
 VALUES
 ('a', null, null, null);

INSERT INTO dbo.Customer_Without_NullValues_VaryWidthType
 (Name, Details1, Details2, Details3)
 VALUES
 ('a', REPLICATE('a', 4000), REPLICATE('a', 4000), REPLICATE('a', 4000));

INSERT INTO dbo.Customer_With_NullValues_VaryWidthType
 (Name, Details1, Details2, Details3)
 VALUES
 ('a', null, null, null);

GO 100000

In order to see the space usage, easiest way is, check number of pages read for data retrieval.

As you see, space usage of First and Second table is same regardless of the value stored. I means, Fixed data types need the defined space whether the value is null or not. However, Third and Forth clearly shows that it is not the same with data type with vary length. When the data type is vary in length, it does not need the space defined with the type.