Sparse columns and Filtered indexes
I attended this course taught by Kimberly Tripp. I attended it a little late, so pardon the few notes.
Tradeoffs of sparse columns: access non-null sparse columns is more costly(Storing non-null sparse columns takes 4 an extra bytes per value. ). But the overall performance gain can’t be beat. It doesn’t have to return all the metadata. It is fast and indexable.
When you create the new sparse column do your research: use the new feature that goes hand and hand with sparse columns (sparsecolumns xml column_set for all_sparse_columns ) which enables you to pull back data. You cannot add it to a table that already has sparse columns
it’s better to create a new table and insert your data due to the internal marking of ‘metadata no longer here’ when you drop a column. It will stay that way unti you do a rebuild. Absolute recommendation. don’t convert tables to have new columns. Insert with a select.
How are sparse columns searchable? With nonclustered filtered indexes
good examples of filtered indexes:
- where active = 1
- where year = 2004 (for like an oltp database where you want all current transactions to not have an index)
- where overthecountersales have id = 1
sp_helpindex doesn’t have all the extra information that applies to filtered indexes so she wrote a new one. Check out sp_helpindex2 from her blog.
