Scalar UDFs vs Inline TVFs: Measuring the Damage
Scalar user-defined functions are the most natural thing in the world to write. You have a bit of logic, you wrap it in a function, you call it in your SELECT list, and your query…
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…
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.