Fixed Length Fields and the NULL Value

Before each muskie fishing trip I go down into my basement and choose which lures I want to bring with.  You see, muksy lures are BIG, and I have many more lures than fit inside my tackle box.  I need to plan which lures will probably be most effective depending on where I will be fishing and under what conditions (weather, season, water clarity, etc.).  I may choose to use every slot in my tackle box, or to just grab a few proven lures and leave the rest of the slots empty.  However, whether I choose to completely fill my tackle box, or just bring a few lures that I really trust, my tackle box is still going to take up the same amount of space in my boat.  Fixed length fields in SQL Server work the same way.  No matter what value (or lack of value) is in them, they take up the same amount of space. Mostly Empty Tackly Box

This topic is a fairly simple one but it resonates with me because it was one of the first situations I ran into as an early DBA where I had to disagree with one of the “more seasoned” DBAs.  I put more seasoned in quotes because the guy (I’ll call him Larry) actually had only been a DBA for about ten months more than me.  In discussing table design, Larry made the point that NULL values don’t take up much space, usually a single bit that indicates the field is NULL.  I tried to point out that this was only true for variable length fields – fixed length fields always take the same amount of space regardless of what value they hold.  Larry disagreed and shot me down abruptly.  I tried to argue my point one more time, then I let it go.  Afterward I set up an experiment to prove to myself that I was correct.  To set this up, I created a table with both fixed length and variable length fields:

CREATE TABLE [dbo].[NullsUsedSpace]
    (
      [ID] [INT] NOT NULL IDENTITY(1, 1) ,
      [IntegerField] [INT] NULL ,
      [VarcharField] [VARCHAR](36) NULL ,
      [CharField] [CHAR](36) NULL
        CONSTRAINT [PK_NullsUsedSpace]
        PRIMARY KEY CLUSTERED ( [ID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

From Microsoft here and here, we know that Integer and Char are fixed length fields while Varchar is a variable length field.  Next I fill this table with a set of various values for each data type:

SELECT TOP 100000
        IDENTITY ( INT,1,1 ) IntegerField ,
        NEWID() VARCHARField ,
        NEWID() CHARField
INTO    #temp
FROM    MASTER.dbo.syscolumns sc1 ,
        MASTER.dbo.syscolumns sc2

INSERT  INTO dbo.NullsUsedSpace
        ( IntegerField ,
          VARCHARField ,
          CHARField
        )
        SELECT  IntegerField ,
                VARCHARField ,
                CHARField
        FROM    #temp;

DROP TABLE #temp;

Here is a screenshot of the data returned:

SELECT  *
FROM    dbo.NullsUsedSpace;

20141222 Nullable Table

I check out the size of the table and find it at almost 9 MB.

20141222 Initial Space

Next we update the table and set all CharField and IntegerField to NULL.

UPDATE  dbo.NullsUsedSpace
SET     CHARField = NULL ,
        IntegerField = NULL;

Depending on the size of the table, you may need to rebuild the index to see size changes:

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

If fixed length fields didn’t need the same amount of space for NULLs as non-NULL values, I’d expect the space used to go down significantly.  Instead, we see it hasn’t changed:

20141222 Nullable Fixed

However, if we set the variable length fields to NULL, we do see the space used go down to just over 5 MB:

UPDATE  dbo.NullsUsedSpace
SET     VARCHARField = NULL;

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

20141222 Nullable Variable

For one last double check, let’s add the integer and char values back into the table to verify the size is still the same.

UPDATE  dbo.NullsUsedSpace
SET     IntegerField = RAND() * 100000 ,
        CHARField = 'Fishing Rocks!';

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

20141222 Nullable Variable 2

So there you have it!  Again, this was a pretty simple concept.  This demonstration proved that NULLs take just as much space in a fixed length field as any other value does, just like an empty tackle box takes up just as much space in a boat as a full one.

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