Sparse Columns and Filtered Indexes

At the December meeting of SLODUG, the Slovenian Developers User Group, I've presented two new functionalities introduced with SQL Server 2008:

• Sparse Columns – a new (or, at least, an improved) way of storing data in nillable columns so that non-existent data takes up virtually no space in the database.
  
  Well, to be honest, storage usage *is* affected (as the maximum size of a row in a table is decreased from 8,060 Bytes to 8,018 Bytes) but with this change the maximum number of columns in a table is increased to 30,000 (including a former maximum of 1,024 non-sparse columns).
  
  With the introduction of sparse columns SQL Server 2008 recognizes two types of tables: wide tables (containing at least one sparse column) and non-wide tables (containing no sparse columns, only "regular" ones – nillable or not);
• Filtered Indexes – by introducing the WHERE clause to the definition of indexes (and statistics) on base tables restrictions can be applied to data sets being indexed, thus introducing several new possibilities, such as:
  
  – sparse keys (nillable/sparse columns can now be used as keys);
  
  – conditional constraints (unique constraints can be created on subsets of data); and
  
  – this also extends the usability of covering indexes (by indexing one or more subsets of data one could effectively partition data without having to use Table Partitioning).

More on these two subjects (and why they actually represent the same one) is available in the presentation. Clue: Rational Flexibility.

(Or rather the files *will be* available as soon as they've been uploaded to the SLODUG site.)

Stay tuned...

ML