Space used by nulls in database
Storing a NULL in a column does not specifically cost or save space. For fixed-length data, the entire space is still reserved.
On the other hand, variable-length data requires only the length of the data plus overhead to store the actual length. For example, a VARCHAR(n) will use 2 bytes of data to indicate that actual length, so the storage space required is always n+2.
Additionally, it should be mentioned that if SET ANSI_PADDING ON, a char(n) with a NULL value will behave as a VARCHAR(n).
No matter what, you will not recognize space "savings" from storing NULLs when using SQL Server 2000 or SQL Server 2005. SQL Server 2008 introduces the concept of a sparse column, which can yield savings for columns that are primarily NULL.
SQL Server has a Bit to indicate NULL. There is no such bit used if the column is defined as NOT NULL
VARCHAR uses variable length to store data (and thus has overhead of indicating how long the actual data is), whereas CHAR is fixed width.
So on that basis a CHAR(1) NOT NULL is "shorter" than a VARCHAR(1) NOT NULL as VARCHAR needs a length indicator, and CHAR will always use just one byte.
EDIT: Note that having a BIT field that allows NULL requires two bits to store it! I often see BIT fields where this has not been considered, don't need to store NULL but have not been set to NOT NULL so are wasting a bit unintentionally
In Oracle
, it depends on type of the column and its position in the row.
If the NULL
columns are last in the row, then they don't take any space at all. Oracle prepends the total row size to each row, everything that doesn't fit is considered NULL
.
If there is some non-NULL
data after a NULL
column, then the NULL
is stored as a single byte of 0xFF
(that is, NULL
type).
Empty VARCHAR2
is equivalent to NULL
. If you test the type of a literal NULL
returned from SELECT
list, it will give you a VARCHAR2(0)
.