Database Animations: Why Big Columns May Not Affect Logical Reads

Over the years, tables – like your waistline – tend to get bigger. We keep tacking on more and more columns, one at a time, in order to handle app needs. It’s easier to add “just one more column” than it is to break things off into a whole separate table.

When you’re only handling a few rows at a time, like transactional insert/update/deletes and one-row selects, the overhead of these additional columns isn’t a big deal. SQL Server can dive into that one row and just fetch it, and since it sits on a single 8KB page anyway, the number of columns doesn’t affect single-row operations.

However, when you need to read multiple rows, the more rows you need to read, the more these extra columns will affect the overhead of the operation. This kinda thing is best illustrated with one of my Database Animations showing the difference between an index that only has Id and DisplayName, versus one that includes a bunch of wider string columns:

I am amused by the AI’s final comment: “wide columns ride free on seeks.” Alrighty then. That certainly sounds like something I’d say. (I use Claude Code to build these animations: we storyboard them out together, and then it handles the details, and surprises me with little tidbits like that.)

As your rows get larger and larger – either due to more columns, or wider columns like JSON and XML, or both – SQL Server is forced to keep an eye on each row’s length. If the row can’t fit on an 8KB page, SQL Server automatically moves that data off-row.

As long as you’re not touching that off-row column – like if you’re not selecting or updating it – then the off-row column doesn’t impact the number of reads you need to do. That’s pretty cool, and it means that I don’t mind if people just store JSON data without manipulating it, and they only fetch it when they need it.

If you wanna be proactive, and if you’re sure that most operations don’t need those large columns, you can even tell SQL Server that you want large columns stored off-row by default, even when the row sizes are small. Check out sp_tableoption:

Let’s get animated. On the left side, we have a table where all the wide columns stay on-row, and on the right, we’ve used sp_tableoption to force them all off-row, onto their own pages linked by a pointer:

As long as you’re not selecting *, this option makes more sense, especially for big string columns like JSON, XML, and (N)VARCHAR(MAX) that you only grab when you’re pulling specific individual rows out of the database. The Users.AboutMe column is a great example: we ain’t running reports on AboutMe contents, nor using it for filtering, just outputting it when rendering a specific user’s profile page.

That sp_tableoption setting only takes effect on newly inserted/updated rows. If you want it to apply to the stuff that’s already in the tables, you’ll need to do an index rebuild.