Feb. 16, 2011, 10:34 a.m.
posted by blackhat
When you're creating a table, you can expect that each variable-length column you define will take at least one byte (and up to four bytes) extra storage for a size or offset value. The other likely per-column overhead is the storage for NULL. Because NULL is not equal to any other value, it can't be stored in the space allotted for the column—extra storage is required for a special flag indicator that says "this is NULL" or "this is not NULL." For example, in IBM or Ingres databases, this flag is one byte long and precedes the column, while in MySQL databases the flag is one bit per nullable column, rounded up to the nearest byte. And as we said earlier, nullable columns in Sybase databases and older versions of Microsoft are automatically variable-length.
This is horrific: What should be a mere "constraint" has become an unchangeable "physical storage descriptor." As a result, you will often have a hard time changing a column from nullable to NOT NULL, or vice versa.
NULL is the only constraint that affects storage. That's a little disappointing, because other constraints could also be used to good effect. For example:
Both of these suggestions are examples of storing a short code to represent a long value. This practice is rapidly becoming archaic because you can do a better job with a user-defined data type (UDT).
The Bottom Line: NULLs
NULL costs overhead. Because NULL is not equal to any other value, it can't be stored in the space allotted for the column—a special flag indicator that says "this is NULL" or "this is not NULL" must also be stored.