Sunday, December 21, 2008

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...


Some Old News

This may be old news, but I haven't said anything about this so far, so this may not be old news to everyone. ;)

SQL Server 2005 Service Pack 3

SQL Server 2005 SP3 has been released on December 15th 2008. The files (elevating the full version number to 9.00.4035) are available for download:

Before applying SP3 you should be aware of the fact that not all Cumulative Updates (CU) for SQL Server 2005 are included in SP3. More information is available in MSDN Knowledge Base article #959195: Cumulative update package 1 for SQL Server 2005 Service Pack 3.

The feature pack for SQL Server 2005 has also been updated:

SQL Server 2005 Books Online Update (December 2008)

The local install version of SQL Server 2005 Books Online has also been updated on December 16th 2008. The files are available for download: