What affect does null in a database have?

Database vary in implementation and how they handle the querying and storing of the data.

Does having null valued attributes consume storage space?

Does having null valued attributes have an effect on the speed of querying of the data? Basically would querying be faster or slower if the data was present or not. Note. I'm not asking how to speed up a querying of data.

So my question is a general one but feel free to answer for a specific database imple开发者_开发知识库mentation like sql server or sqlite.

In the databases I'm aware of, a NULL value doesn't consume any more space than a non-NULL one, it still has to allow for a maximum size. However, the fact that a column is NULLable may consume extra storage.

That's because, in addition to the possible values, you also have to store the fact that the column is NULL or not for each row. However, that's pretty efficient in terms of storage.

For a NULLable column, the extra time taken for a query would be minuscule at most.

You should not really care whether it takes more time. The design of a database is driven by the requirements of the data, not how fast it runs. I don't mean you should ignore performance altogether but, if your column needs to store NULL values, then it needs to, regardless of the performance hit.

Regarding the disk space, what is following applies to SQL Server (probably, but not sure, might apply to other engines).

Fixed size datatypes store a NULL value as the default datatype size: 0 for numbers, '' for characters. Say you have a CHAR(5) column: with a NULL value you are going to fill 5 bytes.

Variable size datatype stores a NULL value with an empty value. Say you have a VARCHAR(10) column: with a NULL value you are going to fill 0 bytes.

Regarding queries, for all databases it depends on the query. For some commands a comparison is made, for some others no. The first table from this SQLite page should satisfy your curiosity.

Yes, NULL does indeed take up storage space. Usually the field uses one bit to store the NULL status, so all rows in the table uses this extra bit regardless of if they are NULL or not. This is the only way to differentiate between NULL and other values (e.g., between NULL and 0).

If this is of concern to you, make sure to mark fields as NOT NULL where you don't need the extra functionality.