Developing Matt

My Technical Journal

Sparse columns and Filtered indexes

leave a comment »

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.

Advertisements

Written by matt

March 25, 2009 at 9:59 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: