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.
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]
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;
I check out the size of the table and find it at almost 9 MB.
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:
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;
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;
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.