Nice article, but under performance you say in regards to VARCHAR:
"NULL and empty string both take up the same space (zero bytes)"
It was my understanding that a varchar, regardless of null or empty string value, will always take up the space of 2 bytes in the row for the offset to the end of the string.
But this may have changed?
Thanks for your attention to detail here! If we are going to go into further detail (which is typically our jobs :-), then you're definitely right---nothing in this world is free, not even a NULL value.
For a fixed-length column, you are going to pay the price to store data there, even if it's NULL, all the time. The space is allocated up-front, regardless of what we store in there.
For a variable-length column, such as VARCHAR, 2 bytes are used as a row-offset and are allocated no matter what we store in there. If we want, we could also include details on how the NULL bitmap works, though the space used there is trivial compared to everything else we have discussed.
This does NOT take into account sparse columns, in which space is allocated up front to compress details about the storage of NULL values. In this case, the efficiency of storage for NULL values increases the more NULL values we have.
The intent of this article was to discuss design considerations rather than get too deep into storage internals, which may distract from why we would do something in the first place. I left all that out for fear of this getting way, way too long 🙂