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.

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.

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.

Should we use Compress function on string values to reduce the storage cost?

While designing my new database in Azure as an Azure SQL Database, was thinking to manage the cost for the storage, hence thought to apply COMPRESS function where-ever possible even though it does not drastically reduce the cost in terms of finance. However, there are two main things to consider if data is going to be compressed using COMPRESS function;

• Compressed data cannot be indexed.
• Compressed data has to be stored in a varbinanry(max) column

If above facts violates the business requirements, then it is not possible to use the function for compressing data. But assume that the column does not need to be indexed and no harm of storing data in compressed format, then it can be simply applied. Now the question is, will it be useful with all values stored in this particular column?

For that, I made a simple test. See code below and the output of it.

DECLARE @Value1 varchar(8000) = 'Compresses the input expression using the GZIP algorithm.'
DECLARE @Value2 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max)'
DECLARE @Value3 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.'
DECLARE @Value4 varchar(8000) = 'Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.Compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max). Compressed data cannot be indexed. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. For automatic compression at the row or page level during storage, see Data Compression.'
 
 SELECT DATALENGTH(@Value1) LengthOfOriginalValueOfValue1
 , DATALENGTH(COMPRESS(@Value1)) LengthOfCompressedValueOfValue1
 , DATALENGTH(@Value2) LengthOfOriginalValueOfValue2
 , DATALENGTH(COMPRESS(@Value2)) LengthOfCompressedValueOfValue2;

 SELECT DATALENGTH(@Value3) LengthOfOriginalValueOfValue3
 , DATALENGTH(COMPRESS(@Value3)) LengthOfCompressedValueOfValue3
 , DATALENGTH(@Value4) LengthOfOriginalValueOfValue4
 , DATALENGTH(COMPRESS(@Value4)) LengthOfCompressedValueOfValue4;

This clearly shows us that we do not get benefits with all types of values stored and it shows and compression works well only with larger values. Therefore I decided to apply this only for few columns. If you have the same requirements, make sure you apply the compression only for columns that has larger values.