Press "Enter" to skip to content

Metadata-Only Column Changes with SQL Server 2016

Paul White takes us through several metadata-only changes which SQL Server 2016 introduced:

These changes can be metadata-only because the underlying binary data layout does not change when Column Descriptor row format is used (hence the need for compression). Without compression, row store uses the original FixedVar representation, which cannot accommodate these fixed-length data type changes without rewriting the physical layout.

You may notice that tinyint is omitted from the integer types list. This is because it is unsigned, while the other integer types are all signed, so a metadata-only change is not possible. For example, a value of 255 can fit in one byte for tinyint, but requires two bytes in any of the signed formats. The signed formats can hold -128 to +127 in one byte when compressed.

This is very interesting, but note the long list of requirements for it to work, notably that compression must be enabled on all indexes and partitions.