Sparse Columns
What is Sparse Column?
Sparse columns are introduced with Microsoft SQL Server 2008 to sql developers and database administrators.
Sparse columns are a new enhancement in data storage and optimizes the size of total amount of data size for null values in MS SQL Server 2008.
In a SQL Server database null values consume from data storage resources although they do not store any data inside.
The sparse column enhancement actually reduces the data sizes required for null data columns in SQL Server 2008 database tables.
When to use Sparse Columns in a SQL Server 2008 database table
Although the definition of a Sparse Column states that using sparse column reduces the size of null values,
if the null data or the rows that have null data in the related column is in small amounts when compared with the rest of column data that are not null,
the total size of the table or the space used by the table may increase.
This is like the pros and cons.
As database developers or sql server administrators, if we use the sparse property of a table column for columns that are not well-tailored for using sparse column,
this will produce a negative affect in the total size of space used by the database table.
Is Sparse Column Property
If you use the Microsoft SQL Server Management Studio for modifying a database table properties, you can set the Is Sparse column property of any table column to Yes in order to use the related columns as a Sparse column.
How to create a database table with Sparse Columns?
Creating tables with sparse columns is very similar to creating any ordinary table column. With only one difference.
That is sql developer or the administrator should identify the column as SPARSE NULL.
Below you can find a sample t-sql code to create a database table with sparse column.
CREATE TABLE SampleSparseColumnTable
(
Id int IDENTITY(1,1) NOT NULL,
SparseColumn1 nvarchar(max) SPARSE NULL,
SparseColumn2 nvarchar(4000) SPARSE NULL,
SparseColumn3 varchar(100) SPARSE NULL,
SparseColumn4 int SPARSE NULL,
SparseColumn5 uniqueidentifier SPARSE NULL
)
Just as using CREATE TABLE syntax, ALTER TABLE statement can also be used to change the Is Sparse property of a table column.
Below I copied down a sample sql code changing a nullable table column into sparse column. First create an ordinary sql database table.
CREATE TABLE SampleSparseColumnTable2
(
Id int IDENTITY(1,1) NOT NULL,
SparseColumn1 bigint NOT NULL,
SparseColumn2 ntext NULL
)
Next, we can run the following t-sql ALTER TABLE ALTER COLUMN statement.
ALTER TABLE SampleSparseColumnTable2 ALTER COLUMN SparseColumn1 bigint SPARSE NULL
Let's run the above t-sql ALTER COLUMN command for the other sample table column.
ALTER TABLE SampleSparseColumnTable2 ALTER COLUMN SparseColumn2 ntext SPARSE NULL
The above t-sql statement unfortunately will not work, it will fail just because of the restrictions with Sparse Columns.
Here is the error message that the SQL Engine will throw:
Msg 1731, Level 16, State 1, Line 1
Cannot create the sparse column 'SparseColumn2' in the table 'SampleSparseColumnTable2' because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.
Restrictions to be taken into consideration with using Sparse Columns
You can not define a sparse column for columns with any of the following data types text, ntext, image, timestamp, geometry, geography.
You can not define a sparse column for columns with user-defined data types.
You can not define a sparse column for columns with FILESTREAM attribute is set.
You can not define a sparse column for an IDENTITY column or ROWGUIDCOL.
Sparse columns can only be defined with nullable columns, can not be declared for columns with marked as NOT NULL.