How much size "Null" value takes in SQL Server

views:

220

answers:

+4 Q:

How much size "Null" value takes in SQL Server

Hi,

I am having a large table 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."

http://stackoverflow.com/questions/2116719/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.

+6 A:

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.

Mark Byers 2010-09-16 22:05:37

@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.

Rocky Singh 2010-09-16 22:19:21

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.

JohnB 2010-09-16 22:23:53

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

Matti Virkkunen 2010-09-16 22:29:35

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

Rocky Singh 2010-09-16 22:34:23

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

Matti Virkkunen 2010-09-16 22:45:44

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

Mark Byers 2010-09-16 23:20:37

@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)](http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-null-bitmap-size.aspx) "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."

Martin Smith 2010-09-16 23:51:38

@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.

Mark Byers 2010-09-17 00:06:24

+1 A:

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).

klabranche 2010-09-16 22:07:26

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 ?

Rocky Singh 2010-09-16 22:10:08

+2 A:

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:

If you set a char(10) to NULL, it occupies 10 bytes (zeroed out)
An int takes 4 bytes (also zeroed out).
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.

JohnB 2010-09-16 22:13:08

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.

Gabe 2010-09-16 22:18:26

ansaurus

tags:

views:

answers:

How much size "Null" value takes in SQL Server

related questions