Benefits Of Deprecated Data Types

Raul Gonzalez shows how to get one of the benefits of older, deprecated data types using (MAX) data types:

We can see that our table is managed by two different allocation units, IN_ROW_DATA and LOB_DATA, which means that all data within columns of the data types above, will end up in different pages by default, regardless of the size of the data.

This is the default behaviour for old LOB types, to be stored separately, but new LOB types (MAX) by default will try to get them In-Row if they are small enough to fit.

Having some of those documents In-Row will result in a serious increase in the number of pages to scan, therefore affecting performance.

Note that for the table scan we have used only the IN_ROW_DATA pages, making it much lighter than if we have to scan the sum of all pages.

This might be helpful for some situations, like where you rarely need to get to the LOB data.

Related Posts

Partitioning Nullable Columns

Kenneth Fisher looks at what happens when you use a nullable column as a partition key: So to start with how does partitioning handle a NULL? If you look in the BOL for the CREATE PARTITION FUNCTION you’ll see the following: Any rows whose partitioning column has null values are placed in the left-most partition unless […]

Read More

Data Type Mismatches

Kendra Little gets into why certain data type mismatches force scans of tables while others can still allow seeks: Sometimes we get lucky comparing a literal value to a column of a different type. But this is very complicated, and joining on two columns of different types in the same family without explicitly converting the […]

Read More


April 2017
« Mar May »