from the error message below:
A compressed index is not supported on table that contains sparse columns or a column set column
as I was rebuilding an index and changing the data_compression to page
This was after making good use of sparse column or compression data wonderful scripts.
Question:
what are column set columns and how to find tables with either sparse or a column set column?
Just use the sys.columns table:
SELECT name, is_sparse, is_column_set
FROM sys.columns
Column sets are:
Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. A column set is like a calculated column in that the column set is not physically stored in the table. A column set differs from a calculated column in that the column set is directly updatable.
