Alex Rivera | Logout

How much size "Null" value takes in SQL Server

Asked 2010-09-16T22:01:04.653
143

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.

sql-server t-sql sql-server-2005 null
Edit
Report

2 Answers

32

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

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

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.

answered 2010-09-16T22:13:08.320
3

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.

answered 2010-09-16T22:18:26.947

Your Answer