Hi, this may be seem a basic question but do columns that contain null consume any actual space at a physical level?

nope, none

hi r937, thanks for quick reply!

so for example if i have a table, with say 60 columns - all of which were functionally dependent on the PK & that no transitive dependencies existed either.

if for every insert, i only populated five columns per record entry - this would not have an effect on storage space at all even though 55 columns per entry were null?

The exact answer depends on your specific DBMS. I believe you use Oracle? In Oracle, trailing null columns consume no space, but non-trailling null columns consume 1 byte each. So if your table has columns (col1, ..., col50) and you do these inserts:

insert into t (col1) values ('x');
insert into t (col50) values ('x');

then the first will just consume N bytes (whatever that may be), but the seconds will consume N + 49 bytes - 1 byte each for col1,...,col49.

yes, that's right

tony: this is database concepts and design

in concept, nulls take no space because they aren't there

It's not the nulls that are occupying space, it is the columns! My piggy bank is sadly empty, but it still consume space on its shelf

hi tony, thanks for the reply.

yes i am using Oracle 9.2.

i also think i get what your saying. Hence under Oracle, columns containing nulls that are 'sandwiched' by columns containing values, will incur a 1 byte value per null column? as these are non-trailling null columns?

is this in the Oracle documentation?

i keep my coins in my pocket, and i assure you, when it's empty, it's empty! what, suddenly my pants have no pocket when it's empty?

but i don't have slots in my pocket for each coin that isn't there, so i don't know why oracle needs them

Correct. Yes, it is in the documentation e.g. in the Concepts Guide.

From a purely logical perspective, Rudy has a case. Most of us live in the physical world, so we need to allow for its non-theoretical (like that phrase?) limitations. Most database engines need something to show that a column is NULL, therefore most engines do use some space somehow to store the representation of a NULL value.

-PatP

my subtle way of suggesting that a question about physical space taken by a NULL should be in the forum for that specific database, not in a forum for concepts

after all it could be as little as a single bit

Tony, when you design tables, do you design to ensure that columns which will often be null are specified as trailing null columns? or is this something that is quite often considered a trivial factor to you?

I would put the not-null columns first when creating an Oracle table, so as not to use up space unnecessarily. But I'm quite happy to add further not-null columns later at the end if needed: I wouldn't immediately feel the need to re-build the table unless it was a really big and super-critical table.

Tony is right.

Nulls cause (in the physical world) more than just a space expense. The treatment required to (a) store and (b) process them is expensive (variable length fields are slower than fixed length). In the Sybase world, it is well established that databases intended for high performance, do not allow Nulls, due to the expense. There are several ways around this (to allow for the "Undefined" value or row) in a physical HP database, all of which conceptually allow the "Undefined" occurence but not the physical implementation of it.

But in a General, design, sense, it is good practice to avoid nulls, just because they lead to outer joins, incomplete resolution of the normalisation process, etc. The point is the occurence of the Null value has to be handled by the code, in any case. The utopian goal therefore is to not have Nulls, and thus allow for code that does not have to be sensitive to Nulls.