Sparse Columns in SQL Server 2008

  • Article

Introduction

In the real world customer scenarios, the customer data can be of heterogeneous and semi structured stored inside SQL Sever tables. In such tables, the data can have different properties applying to different set of subsets of rows. In such cases, SQL Server tables contains columns which are populated with values only for the subset of rows in the tables (Sparse Columns), though these subsets can vary from column to column. Also when a new type of content needs to be added to the table then there is a need to add new properties (columns) which applies to new content type. This requires the need to have more number of columns in a table.

Previous versions of SQL Server don’t have a support of having more than 1024 columns in a single table.

Creating a table with Sparse Columns

Example:

 

create table sparsedtable

(

 

c1 int primary key,

c2 int null,

c3 int default 0,

c4 int sparse,

c5 int sparse,

c6 int sparse,

cs xml column_set for all_sparse_columns)

A column can be declared sparse by specifying the ‘SPARSE’ after the data type in the table definition as shown above. (c4, c5, c6 are sparse columns).

In this manner, a table can contain sparse columns of up to 30000. Though a table contains 30000 sparse columns the maximum number of non-sparse columns in a table is still 1024 only.

The last column in the above table denotes a column set which stores data (only if data is not null) of all the sparse columns in a xml format. Only one column set column can be created in a table. A column can be declared as column set by defining COLUMN_SET as shown in the above T-SQL.

Note: Column set column is a non sparse column

Advantages of Sparse Columns

1. If the value of a column is NULL, it doesn’t consume space at all.

2. Support of having 30000 sparse columns in a table.

3. It stores the data in a single xml column but for an external application it behaves like a normal column.

4. SPARSE column can take advantage of filtered Indexes, where data are filled in the row.

 

Limitations

There are certain limitations as well.

1. All the data types cannot be sparse. Text, NText,Geometry, Geography, timestamp, user defined datatypes, varbinary(max), filestream attribute column.

2. Sparse Column doesn’t have IDENTITY or ROWGUIDCOL Property

3. Sparse Column cannot have a default value or rule or computed column.

4. Sparse column cannot be party of clustered index key. Also it cannot be added as an indexed column for unique index as well.

5. The maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes if a table contains a sparse column.

 

Columns set characteristics

Tables with sparse columns can have defined column set to manipulate all sparse columns as a structure. The column set is untyped XML column and is treated as any other XML column.

- It should be of XML Datatype

- Only one column set per table is allowed.

- Cannot be added if the table already contains sparse columns.

- Limited by XML data size limit of 2GB

- Cannot have constraints or default values.

 

Query Editor

 In order to query all the columns in the table (containing sparse columns),

select * from <tablename>

When we execute the above T-SQL , it shows all the non sparse columns including the column set column .

But we can able to query the sparse columns by specifying the column names explicitly in the SELECT query.

For example if the column names of sparse columns in a table or sp1,sp2 then

select sp1,sp2 from <tablename>

It returns the values of those columns only.

Creating sparse columns using SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio and connect to the server.

2. Expand databases and select the ‘Tables’ node.

3. Right click the Tables node and click ‘New Table…’ from the context menu launching table designer.

4. Enter the column names, data types for all the columns. Each column has a set of properties as shown in the column properties.

5. If a column to be declared as sparse, set the Is Sparse to Yes in the column properties for the particular column

6. Similarly if a column needs to be declared as column set, set the Is Columnset property to Yes in the column properties.