I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.
At a high level you could think: I can just put a pointer at the end that points to the new column. However, at the page level aren't data pages filled with individual records? Wouldn't adding a column mean that every page that was already full would require a split?
Even pages that weren't full would require a lot of data juggling to add that column to the end of each record, update all the slot arrays, then cascade all the pointer changes through any existing indexes and/or the IAM and GAM pages?
The only thing I can think of is that all new column data is added to new pages, without the rest of the record, and pointers are added throughout the table tree structure to reference the new column pages. However, this seems like it would ruin spatial locality. If this is it, does the DBMS juggle data behind the scenes even when we don't specifically request a REBUILD
?
I'm talking about the bit level of DBMS memory management with pages and asking how DBMSs are able to add a column (with or without allowing NULL values) to a set of existing records so quickly, even though the records already exist as a set of bits in a data page.