SQL sparse columns

Background

The purpose of this article is to provide a guide on how to use SQL Server wide tables and sparse columns to enable more than 1024 columns in SQL server table for Highstage.

SQL server table width limitations

SQL Server supports a maximum of 1024 Columns in a non-wide table. To exceed the 1024 limitation wide tables and sparse columns must be used. SQL Server supports a maximum of 30,000 Columns in a wide table, of which maximum 1023 columns can be non-sparse.

SQL server sparse column limitations

A SPARSE column provides optimized storage for NULL values. A sparse column cannot have a default value. Default value is null. Sparse column takes up more storage and performs less efficient that non-sparse columns.

Guides

Enabling sparse columns in Highstage

Add the following parameter to custom.schema.xml to enable sparse columns in Highstage:

xxxxxxxxxx
  <parameter name="SqlSparseColumns" value="1" />

The parameter will make sure that Highstage prepares SQL server for sparse columns.

If table contains sparse columns definitions in Highstage schema then the table will be made wide by including an XML column with the name SPARSE_COLUMN_SET.

Note: Remember to reset Highstage for all changes to take effect.

Create a new column in Highstage as a sparse column

Any new column in Highstage can be created as a sparse column by including the property sqlsparse="1" to the column definitions in custom.schema.xml.

The following sample demonstrates a newly created Highstage sparse column:

xxxxxxxxxx
<column name="SparseVarchar" sqltype="varchar" sqlsize="50" sqlsparse="1" />

Note: Note that the column must be defined within an appropriate <type> element in custom.schema.xml.

For all changes to take effect, please do the following:

• Navigate SYSTEM > Schema and select Apply for the newly created columns to be created.

Important: It is important to note that you cannot convert existing sparse columns to non-sparse or non-sparse to sparse columns through Highstage schema configurations.

Changing from sparse to non-sparse or non-sparse to sparse

Changing a column from sparse to non-sparse or non-sparse to sparse requires changing the storage format of the column in SQL Server Management Studio.

To be able to change from non-sparse to sparse the column must be nullable and have no default value:

The SQL Server Management Studio uses the following procedure to accomplish this change:

1. Adds a new column to the table in the new storage size and format.
2. For each row in the table, updates and copies the value stored in the old column to the new column.
3. Removes the old column from the table schema.
4. Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

Enforcing default value on sparse columns

It is not possible to specify column default values on sparse columns in SQL server. Also SQL server sparse columns are optimized for NULL as default value. Using any other value as “default” introduced by custom code will have a performance penalty.

Using columns with null values in refinery and custom code

Make sure to use the ToString() method when accessing SQL column data that may contain null values.

The following snippet will be successful:

jobInfo.appInfo.RefineSetProperty(jobInfo, "SparseColumnVarchar", jobInfo.dr["SparseColumnVarchar"].ToString());

The following snippet will be unsuccessful:

jobInfo.appInfo.RefineSetProperty(jobInfo, "SparseColumnVarchar", (string)jobInfo.dr["SparseColumnVarchar"]);