Impact of NULL values ​​on storage in SQL Server?

Question

If you have a table with 20 rows containing 12 zero columns and 8 columns with values, what are the implications for storing and using memory?

Is it the only one unique or is it stored in memory in the same place every time and simply referenced? Does a ton of zeros take up a ton of space? A table filled with zeros takes up the same amount as a table, same size full of int values?

This is for Sql server.

Answer 1

It depends on the database engine, as well as the type of column.

At least SQLite stores each empty column as a "null type" that does not accept any additional space (each record is serialized into one memory location for storage, so in this case there is no room for a non-empty value). With these optimizations, NULL has very little storage overhead. (SQLite also has optimizations for values ​​0 and 1 - database developers do not play!) See 2.1 Record format for details.

Now things can get a lot more complicated, especially with updating and potentially fragmenting the index. For example, in SQL Server, space can be reserved for column data depending on the type. For example, int null will still reserve space for an integer (and also somewhere there is a "null" flag), however varchar(100) null does not seem to reserve space (this last bit from memory, so warned!).

Happy coding.

Answer 2

Starting with SQL Server 2008, you can define a column as SPARSE when you have a ton of zeros. This will save some space, but this requires that part of the column values ​​be zero. Exactly how much depends on the type.

See tables for estimating spatial savings by data type in the article “Using Sparse Columns,” which indicates what percentage of values ​​should be zero for a net savings of 40%

For example, according to the tables, 98% of the values ​​in the bit field should be zero to get 40% savings, while only 43% of the uniqueidentifier column will show you the same percentage.