Menu
If a column is null, does that affect the space used by the column? Is the space used fixed by the column definition? Does this vary from database to database. (I am mainly interestred in SQL Server 2000.)
Clarification: The question relates not to what happens when the column is 'nullable' (This costs another bit as Kritsen & gbn pointed out). The question is, is there any saving when the column is actually null (in some particular row).
...
Cadaeic provided the answer for SQL Server which seems to be no savings in SQL Server until version 2008, whereas according to Quassnoi you can get savings in Oracle if the null columns are at the end. Thanks for the answers, they were all helpful.
903
They take up space on your database hard drive as opposed to “nothing” that indicates there is no value. It's common for new database developers to think that they are saving hard drive space by adding NULL values, but this isn't necessarily the case.
While a NULL itself does not require any storage space, NDB reserves 4 bytes per row if the table definition contains any columns allowing NULL , up to 32 NULL columns.
Answer: Null indicates there is no value within a database field for a given record. It does not mean zero because zero is a value. Blank indicates there is a value within a database but the field is blank.
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces, spaces are considered as values because they are strings (and sql can't tell what the value of a string means to the user per se), but a NULL signifies a missing value, and hence has no value associated ...
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.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
