how to find tables with either sparse or a column set column? 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.