SQLServer Myth: NULL columns use no space

TL;DR

A table with NULL columns uses exactly the same amount of space as NOT NULL columns. Even indexes using NULL columns do not reduce.

If you really need to save space then the options are

Simply do not let a designer or developer insist on having a column exist in a table ‘just incase’ it is needed. Especially if that table is likely to store millions of records. Yes, “disk space is cheap”, but when a system is heavily used and critical – memory, backups, CPU, transfer times and network traffic start to matter. The more rows you can fit on a SQL page, the better.

Here’s the proof:
Create a new database called test, and run this SQL:
USE 'test'
GO;
CREATE TABLE tableNulls17SizeTest (
int1 INT NULL, int2 INT NULL, int3 INT NULL, int4 INT NULL, int5 INT NULL,
int6 INT NULL, int7 INT NULL, int8 INT NULL, int9 INT NULL, int10 INT NULL,
int11 INT NULL, int12 INT NULL, int13 INT NULL, int14 INT NULL, int15 INT NULL,
int16 INT NULL, int17 INT NULL )
GO

SET NOCOUNT ON

INSERT INTO tableNulls17SizeTest VALUES(
NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL )
GO 1000000

After 6 minutes….
sp_spaceused 'tableNulls17SizeTest'
SELECT page_count, record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(N'test'),
OBJECT_ID(N'tableNulls17SizeTest'), NULL, NULL, 'DETAILED')
GO

Results in:

NullColumnsUseSpace

And if you check the file system, the .mdf file grows to 84mb.

Now, to test that the file is the same size with data, recreate the blank ‘test’ database, then execute this:

USE [test]
GO

CREATE TABLE tableNoNulls17SizeTest (
int1 INT NOT NULL, int2 INT NOT NULL, int3 INT NOT NULL, int4 INT NOT NULL, int5 INT NOT NULL,
int6 INT NOT NULL, int7 INT NOT NULL, int8 INT NOT NULL, int9 INT NOT NULL, int10 INT NOT NULL,
int11 INT NOT NULL, int12 INT NOT NULL, int13 INT NOT NULL, int14 INT NOT NULL, int15 INT NOT NULL,
int16 INT NOT NULL, int17 INT NOT NULL )
GO
SET NOCOUNT ON
INSERT INTO tableNoNulls17SizeTest VALUES(
RAND()*10000000,RAND()*10000000,RAND()*10000000,RAND()*10000000,
RAND()*10000000,RAND()*10000000,RAND()*10000000,RAND()*10000000,
RAND()*10000000,RAND()*10000000,RAND()*10000000,RAND()*10000000,
RAND()*10000000,RAND()*10000000,RAND()*10000000,RAND()*10000000,
RAND()*10000000 )
GO 1000000

sp_spaceused ‘tableNoNulls17SizeTest’

SELECT page_count, record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(N’test’),
OBJECT_ID(N’tableNoNulls17SizeTest’), NULL, NULL, ‘DETAILED’)
GO

And the result….

CollumnsNonNullWithValues

I also tested the size of indexes with respect to nullable columns

CREATE NONCLUSTERED INDEX ix1 ON tableNulls17SizeTest ( int1,int2 )

CREATE NONCLUSTERED INDEX ix1 ON tableNoNulls17SizeTest ( int1,int2 )

And they had the same size on disk once built.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s