How much size "Null" value takes in SQL Server

Question

I have a large table with say 10 columns. 4 of them remains null most of the times. I have a query that does null value takes any size or no size in bytes. I read few articles some of them are saying :

http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-4.html

There is a misconception that if we have the NULL values in a table it doesn't occupy storage space. The fact is, a NULL value occupies space – 2 bytes

SQL: Using NULL values vs. default values

A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.

Can you please guide me regarding the size taken by null value.

Bloodless

Answer 1

If the field is fixed width storing NULL takes the same space as any other value - the width of the field.

If the field is variable width the NULL value takes up no space.

In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.

---

The reason for the discrepancies that you have observed in information from other sources:

• The start of the first article is a bit misleading. The article is not talking about the cost of storing a NULL value, but the cost of having the ability to store a NULL (i.e the cost of making a column nullable). It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns).

• The second link seems to be a question about Microsoft Access. I don't know the details of how Access stores NULLs but I wouldn't be surprised if it is different to SQL Server.

Diamond

@Mark"It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns)" By this you mean to say it takes 1 bit as the size taken in memory for variable datatypes. – Bloodless

The smallest addressable unit of memory in most computer systems is one byte (typically 8 bits). So in reality, a bit takes a byte. Great answer Mark: +1. – Winser

However, a second bit, and a third bit, and all the way up to an eighth bit fit in the same byte. – Wop

@Matti you mean to say that in single byte memory space we can have 8 null values ? – Bloodless

@Rocky Singh: Indeed we could. I don't know which database servers do so though. – Wop

@Martin Smith: Yeah the way I wrote that part could have been interpreted incorrectly. I've reworded slightly. I hope it is clear now. – Diamond

@Mark - Yes that looks a lot clearer. Apologies for the disappearing comment. I meant to revise it but my Internet Connection went down between deletion and submission! It also depends a bit (From the comments section here) "For heap and clustered index record, there's always a NULL bitmap . For non-clustered indexes, there won't be if all columns in the index are NOT NULL." – Rothenberg

@Martin Smith: I didn't know that. That makes things more complicated because if I understand it correctly it means that making a column nullable doesn't increase the storage space required (because the null bitmap is always present) unless that column is also in an index and the other columns in the index are not nullable. In this case the index must now include a null bitmap. – Diamond

What about usage of TEXT/NTEXT types? They can't have a variable length...so ... does "If the field is fixed width" addresse those as well? Does it mean NULL values in a text column use as much space as the filled text columns do? – Silencer

@Silencer TEXT/NTEXT are by definition variable length (and deprecated recommending using VARCHAR(MAX)/NVARCHAR(MAX) respectively, instead) – Coinage

Would this be true of floats where no value is assigned to the length argument? I'm not sure if it qualifies as variable length, since it has a default value of 53. Furthermore, this documentation page says the length argument is only treated as 1 one of 2 values, 24 or 53, depending on whether it falls in the 1-24 range or outside it. msdn.microsoft.com/en-us/library/ms173773.aspx ....I can ask a separate question if that would be preferable. – Obedience

These answers make no sense. "If the field is fixed width storing NULL takes the same space as any other value - the width of the field". So if I have a byte, the width of the field is 8 bits. How can MSSQL possibly represent all possible byte values AND null in just 8 bits? Conversely: "If the field is variable width the NULL value takes up no space." So how could MSSQL then tell the difference between a zero-length string and a NULL string? Information theory requires that the NULL value takes up space. How much is it? – Machado

Which datatypes are considered variable length? varchar, nvarchar... are there more? – Maintenance

@Machado The statement is correct, only the width of a nullable field is 1 bit more than the width of a non-nullable field of otherwise the same type (in practice, either 1 byte more if the NULL bitmap needs to be expanded to accommodate this column, or 0 bytes more if there would otherwise be free bits in the NULL bitmap). But the space used will be the same for a NULL value as for any other value (for fixed-width fields). – Maledict

Answer 2

The following link claims that if the column is variable length, i.e. varchar then NULL takes 0 bytes (plus 1 byte is used to flag whether value is NULL or not):

• How does SQL Server really store NULL-s

The above link, as well as the below link, claim that for fixed length columns, i.e. char(10) or int, a value of NULL occupies the length of the column (plus 1 byte to flag whether it's NULL or not):

• Data Type Performance Tuning Tips for Microsoft SQL Server

Examples:

1. If you set a char(10) to NULL, it occupies 10 bytes (zeroed out)
2. An int takes 4 bytes (also zeroed out).
3. A varchar(1 million) set to NULL takes 0 bytes (+ 2 bytes)

Note: on a slight tangent, the storage size of varchar is the length of data entered + 2 bytes.

Winser

Wouldnt a varchar storing a NULL take 0 + 2 + 1(NULL overhead) bytes? – Pleven

It should be + 1 bit to flag NULL. @Akash: 2 bytes shouldn't be necessary as the bitmap already flags the value as NULL (no info would be added). – Skiff

Answer 3

From this link:

Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.

For variable size datatypes the acctual size is 0 bytes.

For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).

Transposal

You mean to say for datatypes like nvarchar(max) varchar(max) Null will takes 0 bytes and for int, chars etc it will take the default size to default values they have ? – Bloodless

Answer 4

Storing a NULL value does not take any space.

"The fact is, a NULL value occupies space – 2 bytes."

This is a misconception -- that's 2 bytes per row, and I'm pretty sure that all rows use those 2 bytes regardless of whether there's any nullable columns.

A NULL value in databases is a system value that takes up one byte of storage

This is talking about databases in general, not specifically SQL Server. SQL Server does not use 1 byte to store NULL values.

Confocal

Answer 5

Even though this questions is specifically tagged as SQL Server 2005, being that it is now 2021, it should be pointed out that it is a "trick question" for any version of SQL Server after 2005.

This is because if either ROW or PAGE compression are used, or if the column is defined as SPARSE, then it will "no space" in the actual row to store a 'NULL value'. These were added in SQL Server 2008.

The implementation notes for ROW COMPRESSION (which is a prerequisite for PAGE COMPRESSION) states:

NULL and 0 values across all data types are optimized and take no bytes¹.

While there is still minimal metadata (4 bits per column + (record overhead / columns)) stored per non-sparse column in each physical record², it's strictly not the value and is required in all cases³.

SPARSE columns with a NULL value take up no space and no relevant per-row metadata (as the number of SPARSE columns increase), albeit with a trade-off for non-NULL values.

As such, it is hard to "count" space without anlyzing the actual DB usage stats. The average bytes per row will vary based on precise column types, table/index rebuild settings, actual data and duplicity, fill capacity, effective page utilization, fragmentation, LOB usage, etc. and is often a more useful metric.

¹ SQLite uses a similar approach to have effectively-free NULL values.

² A brief of the technical layout used in ROW (and thus PAGE) compression can found in "SQL Server 2012 Internals: Special Storage".

Following the 1 or 2 bytes for the number of columns is the CD array, which uses 4 bits [of metadata] for each column in the table to represent information about the length of the column .. 0 (0×0) indicates that the corresponding column is NULL.

³ Fun fact: with ROW compression, bit column values exist entirely in the corresponding 4-bit metadata.

Hyperventilation

Answer 6

At the time of this posting, the most upvoted answer Answer by Mark Byers is mostly correct, but it also states that...

"If the field is variable width the NULL value takes up no space."

That's true only some of the time and the cases where it's true are actually quite rare because of the column order people choose for their tables.

So, the correct answer for SQL Server is, "It Depends".

To summarize (and simplify using only VARCHAR as an example)... NULL VARCHAR columns take ZERO bytes ONLY if there are NO VARCHAR columns to the RIGHT that are populated (not NULL, not "Empty String"). Any and all NULL VARCHAR columns to the LEFT of a populated column will take 2 bytes each for the "Column Offset", which marks the "end byte" for each variable width value in the row.

That means that if you have 10 VARCHARS in a table that are all NULL, they all collectively take ZERO bytes. Add just 1 character to the right-most column and your row expands by a whopping 23 bytes.

 2 for the Variable Width Column Count 
20 for the 10 two-byte "Column  Offsets"
 1 for the actual byte added to the right-most VARCHAR
--
23 total bytes added to the row as a result of adding just 1 byte to the right-most VARCHAR.

Here's the link to an answer here on StackOverflow that shows what happens at the row level using an easy to read, formatted output from DBCC PAGE.

https://mcmap.net/q/161527/-how-many-bytes-are-used-to-store-a-null-value-in-an-nvarchar-n-column

NVARCHAR works in an identical manner for NULLs and "Empty Strings". I have NOT tested with other variable width datatypes but my "unqualified opinion" would be that they would work the same way.

Enchiridion