Reading of fixed-length data types

Question

Say I have a fixed length column and I am SELECTing from it, say 100 rows. When reading different rows of the fixed length column, does SQL Server check the length of the column for every row or does it check it once and reuse this information so that subsequent rows can be read faster?

In contrast, for variable length columns, SQL Server needs to check the length of every variable-length column for every row, using the offset array.

So my question is: Does SQL Server check the length of the fixed-length data types for every row (i.e. after the Status bits A and B portion of the row)? Logically when it needs to read a fixed length column it only needs to check it once.

Is this overhead the reason why indexes are best on fixed-length columns?

Not trying to solve any problem, just trying to understand.

Extra info: Regarding that indexes are best on fixed-length columns: This whole question started for me when I was reading this article Indexing Strategies for SQL Server Performance. At one point it says: "A clustered index key should be narrow but also use a fixed-width data type." What are the reasons for this statement? I can only think of the reason which is related to my question, i.e. fixed-length columns are cheaper to read, because the length only needs to be checked once.

Answer 1

In the FixedVar storage format, each fixed-length column appears at the same offset in every row.

SQL Server caches the offset for each fixed-length column needed, and reuses that information on subsequent row accesses. It doesn't calculate the offset afresh for each row.

Accessing data from the fixed-length portion of the row therefore has a small efficiency advantage over reading variable-length data. It's not as much as you might think, because finding variable-length data involves only a couple of extra CPU instructions on data that is very likely to already be in L1 cache (though not necessarily on the same line).

You might be able to measure a small difference if you test accessing a lot of the same data in fixed vs variable storage. I haven't seen any recent results on modern hardware. There are other overheads associated with row-mode query execution in general that I would expect to dominate over this effect.

In summary: I wouldn't go out of my way to choose fixed-length types over variable-length ones. Choose a data type that works best for the application at hand.

Is this overhead the reason why indexes are best on fixed length columns?

A secondary b-tree index is a separate structure with a copy of the indexed data sorted according to the index keys. The same general considerations mentioned above apply to reading data from index pages as from data pages.

"A clustered index key should be narrow but also use a fixed-width data type."

Secondary b-tree indexes have to include the row locator (clustered index key(s) and possibly a uniqueifier). When the clustered index key includes a variable-length data type, any secondary b-tree index rows may have extra overhead for the number of variable-length columns and the variable column offset array. The secondary indexes might therefore be a bit wider than they need to be. I believe that is the point Paul Randal was alluding to in that quote.