What ROW and PAGE Compression Do to the Record
Data compression is one of the few storage features you can turn on with a single clause and measure the same afternoon. It changes the physical format of the record on the page, not the…
A Tour of SQL Server Page Types: Data, GAM, SGAM, PFS, IAM
Most of this storage series has been spent inside data pages, pulling rows apart byte by byte. But a data file is not only data pages. Scattered through it, at fixed and predictable spots, are…
Forwarded Records: What an UPDATE Can Do to a Heap
A heap, a table with no clustered index, has a failure mode that clustered tables do not: an UPDATE that makes a row larger can force it to physically move, and SQL Server leaves a…
Row-Overflow and LOB: When a Row Leaves the Page
A data page is 8 KB, and a single row is capped at 8,060 bytes of in-row data.[1] So what happens when you declare two varchar(5000) columns, or store a 20,000-character varchar(max), in a row…
How SQL Server Packs BIT Columns Into a Single Byte
A common worry about bit columns is that a table full of yes/no flags will waste a byte on every flag. It will not. SQL Server packs bit columns together, up to eight of them…
The NULL Bitmap: How SQL Server Records Nullability
Every SQL Server data row carries a NULL bitmap, even a table with no nullable columns. Find it on the page, watch the bits flip as columns go NULL, and see what it actually costs.