Null value for SQL Server

Question

In a SQL server, null values ​​take up less space than non-zero values, or equal to NULL, presented as a flag indicating that the value is not stored (and therefore, MORE space is actually required to store null values).

The comparison will not be a null value, not the null values ​​stored in the same column, but the values ​​stored in the nullable column and the actual values ​​stored in the column are not null.

I understand that it may be common knowledge that NULL takes up less space, but with optimization, is there a significant impact?

Answer 1

NULLs for variable-length columns are stored in the NULL raster file that is present in each record with a minimum SQL Server 2000 value: no space is used for the column itself. (Edit: 2 bytes for a length that will be zero, of course)

For fixed-length columns, a NULL raster value means that no sentinel value is required in the space for holding a fixed-length column.

Index rejection, NULL comparisons may be faster because of this and because comparisons with NULL are always UNKNOWN (which drops to false)

Edit:

Paul Randall Inside the storage engine: The anatomy of the record , which shows the structure on disk +, explains the optimization of the zero bitmap + as fixed and variable-length columns are saved

Edit 2: read the question again ...

From a storage point of view, a NULL bitmap might not be an optimization because it adds a byte (or more). However, in practice, he avoids a lot of processing to find NULL values.

Answer 2

The NULL flag is stored at the beginning of each line, but the structure on the disk is a repeating structure of a fixed size.

In other words, if you have

Create Table XX ( Id Int Identity (1, 1) FirstName VarChar (20) NULL, LastName Varchar (20) NOT NULL ) 

Each row will occupy the same amount of space no matter how much data you put into it.

Answer 3

Zero values ​​are stored in flags for a simple reason:

Suppose your table has null values ​​and they do not take up space, it is very beautiful, but as soon as you put some data in this space, you need to move all the data behind you to make room for new data, and the cost of this is more higher than the cost of the unallocated distribution of hard disk space for a field with a zero value.

In short, the databases are designed for environments without problems on hdd, so optimizing this small value is not very important, and therefore each record has a flag with a zero value in it, if it is true, the record is zero, otherwise it has value.

Hope my explanation helps

Best of luck!