NULL, how much memory / space it takes?

[Janne789]

How much memory a NULL value takes in a database?

E.g. if I have understood correctly INTEGER takes (or can take) 4 bytes.
But, if the field has the value NULL, how much space does it take then?
Does it take one bit? (Bool: true or false, ~ on / off ?)
(Or it may also depend on the manufacturer of the DBMS?)

Does someone know how much memory it (NULL value) generally can take?


If there can be e.g. several millions of rows in the database, it may be important to know how much space NULL values take.

[Vaderman]

As a general rule of thumb, try and keep null values to a bare minimum, or if you have them, then ensure that they have a minimal effect on data modification.

You can create yourself an sql statement to fill in these null values to some arbitary value.

It maybe a good idea to re-evaluate your design if you have thousands of rows that have null data columns.

Regards

John

[ovidiucucu]

As a general rule of thumb, try and keep null values to a bare minimum, or if you have them, then ensure that they have a minimal effect on data modification.

You can create yourself an sql statement to fill in these null values to some arbitary value.

It maybe a good idea to re-evaluate your design if you have thousands of rows that have null data columns.

Regards

John
Sorry John, I must disagree.
Let's take a arbitrary example (maybe not the best): we have a table containing data about codeguru members with a field named Birth_date. Now you want to count all members born before 01/01/1970. The problem is that not all members posted their birth date... so you have understood.... we must have a chance to count at least those members are not ashamed of their age. :D ;)

[NatThoelecke]

How much memory a NULL value takes in a database?

E.g. if I have understood correctly INTEGER takes (or can take) 4 bytes.
But, if the field has the value NULL, how much space does it take then?
Does it take one bit? (Bool: true or false, ~ on / off ?)
(Or it may also depend on the manufacturer of the DBMS?)

Does someone know how much memory it (NULL value) generally can take?


If there can be e.g. several millions of rows in the database, it may be important to know how much space NULL values take.NULL is just a value that you can stuff into a data field. The amount of space it takes up does depend upon the DBMS you are using. Some systems, like Access, will use whatever the overall field size is set to (e.g. if the field is integer, 4 bytes). I understand other systems like SQL Server will resize the text data fields for the record to conserve space and only use as much space as is needed for the value entered in the field. So a NULL value in a text field in SQL Server would probably ony take up a single byte at most.

Vaderman and ovidiucucu each have valid points regarding the use of NULL values in a database. If you encounter a considerable number of NULL values for a particular field you may want to separate that field out of that table and into its own table where you only store linked records for non-NULL data values (this will result in a much smaller database). However, you shouldn't shun the use of NULLs altogether, since, in some cases, a NULL value can be just as useful in data analysis as an actual value. I would certainly recommend that, if a particular field needs to have some sort of value for data analysis/manipulation purposes; then, disallow NULL values for the field so your queries don't blow up on you and you don't have to redesign you queries to account for NULLs.

[Klymer]

just to add my 2 cents :)

NULL values are not to be disregarded as "bad"! as logic has it, it definitively says: "the value this field is not defined" - which is completely different to a value of 0 (for numbers) or "" for strings. in our company we had the case that someone tried to use the number 9999 as a dummy value instead of NULL. somewhat bad idea, as the customer said some years later: "Oh I want the value 9999 for this field to indicate a certain state." Ouch, all reports (Acess as frontend) were at once wrong.

To conclude: No bad thing, this NULL, if (!!!) you design your database carefully (but of course we all do that :blush: )

[handa_varun]

As per me,,,it take no space...
eg..
insert some value in a temporaray table with NULL,,

check the db_space befor & after deleting that record.
it shud be same..

Rergards
Varun

[ovidiucucu]

As per me,,,it take no space...
eg..
insert some value in a temporaray table with NULL,,

check the db_space befor & after deleting that record.
it shud be same..

Rergards
Varun
NOPE!
No RDBMS guarantee that after a DELETE statement, the record is also physically deleted from the file where it resides. That's for performance reason: physically deleting the record means reading the entire file, excluding the deleted record then re-writing it again ----> bad performance.

[Janne789]

Thanks for the answers!

I also made a test where I inserted rows to a table.

TABLE:
create table TEMPORARY_TABLE
(field1 INTEGER NOT NULL,
field2 INTEGER);

The size of the database was the same in both cases:
1. db had these kinds of values: int value, int value
and
2. db had these kinds of values: int value, NULL value

I inserted equal amount of rows in both cases.
(The db had equal amount of rows in both cases.)
-> The size of the db was the same in both cases..

br,

[Krzemo]

Space needed for NULL depends greatly on RDBMS system U use. In some systems there is special indicator for that even if field is not marked as nullable.

Generally there are many approaches to that problem:
1) Using special values for indication of null
2) Using additional one (or more) byte indicator for each null column
3) Using bitmap indicator for row
4) Using NULL indexes for null columns.

Best regards,
Krzemo.

PS: Your test indicates nothing :D .
U should create more fileds (ad least 33), insert many values (at least 10000) and compare only used space ( not all space allocated by database since some RDBMS pre-allocates extents to speed up datafile resizing)

[Janne789]

I added hundreds of thousands of rows to the database, but I had only those two fields in the table. I did not have e.g. 33 columns in the table.

I did not look at the size on the disk (the size of the files).
I checked the used space in the database with DBMS's commands.
I checked how much memory the data really took in the database.
(I used "Internal commands" in the DBMS.)

When there was no rows in the database, the size, which was reported by the DBMS, was of course smaller.

Cases:
1. 400 000 rows of int int rows in the table.
2. 400 000 rows of int null rows in the table.

-> The reported size of the database was the same in both cases.

Of course, when the data was removed after testing the case number 1, the size got smaller and was the same as it was before executing case 1.
After I executed case number 2, the size became the same as it was in case 1 too.


I do not know that is the amount of columns important. I did not understand yet how it affects to the result, or I may have understood something incorrectly.

[Krzemo]

I do not know that is the amount of columns important
Yes - if RDBMS uses bitmap NULL idicators for rows. As U know 1 Byte=8 BITs (so possibly 8 null indicators). And because most RDBMS are 32bit systems (There are few 64 bit RDBMS systems) than minimal columns count should be 33 (or 65 for 64 bit system).

You shouldn't test this on one table (with one field allowing nulls and one not) because it won't test structural size difference. U should create table with many fields not allowing nulls in them, and make tests on it. Than drop it and create the same but allowing nulls in them..... Etc. Etc.

Anyhow - What kind of RDBMS U have tested?

Best regards,
Krzemo.