Sparse Columns in SQL Server 2008

A coworker sent this link to me today. It’s an interesting feature for SQL Server 2008 which allows you to save space for nullable columns.

Here’s some of the basic info:

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Sparse columns can be used with column sets and filtered indexes:

  • Column sets

    INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets, see Using Column Sets.

  • Filtered indexes

    Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information, see Filtered Index Design Guidelines.

Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server.

Sparse columns have the following characteristics:

  • The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the value requires no storage.
  • Catalog views for a table that has sparse columns are the same as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.
  • Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.
  • The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. The bits that are returned by the COLUMNS_UPDATED function are as follows:
    • When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.
    • When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.
    • For insert operations, all bits are set to 1.

    For more information about columns sets, see Using Column Sets.




Documenting the Software Architecture (via John Olamendy’s Blog)

Very good article.

Documenting the Software Architecture By: Juan Carlos (John Charles) Olamendy Turruellas In this article, I’m going to talk about specifying the architecture of software intensive systems based on multiple views in order to address different concerns of various stakeholders: end-user, developers, systems engineers, project manager, etc. Software architecture deals with high-level structure of the software system, it means the key components, its relationship and how they expose their … Read More

via John Olamendy's Blog

SQL Server: Loading all the values from a table into a single line (via Tone’s Enterprise Dot Net Blog)

It's a common requirement to load all the values from a table into a single line for consumption. For example, you might retrieve a User record, but you want the list of roles that the user belongs to in a single comma separated line. The usual way of performing this was to create an inline function, pass it the UserId of the record and have it build the list. The problem with that is the inline function – it causes a table scan, which is highly … Read More

via Tone's Enterprise Dot Net Blog