What is a SPARSE Column, How to use sparse columns in SQL Server 2008 ?
How to create table which has sparse column, How to use SQL Server Sparse Columns ?
What is a Column Set, How to create table which has sparse column set ?
How to Insert and update data in a column set and how to Access Sparse Columns and Sparse Column sets
How Sparse columns helps to save Space / Storage ?
The following Video Tutorial answers all above questions
What is a SPARSE Column?
Sparse columns provide a highly efficient way of managing empty space in a database by enabling NULL data to consume no physical space. For example, sparse columns enable object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs.
What is a Column Set?
Tables that contain sparse columns can define a column set to return the data in all of the sparse columns in the table. Similarly to a computed column, it is not physically stored in the table; however, unlike computed columns, the data in it is directly editable. The column set returns an untyped XML representation of the data. Column sets are useful when you have a large number of sparse columns in a table and working with them is awkward.
How to use SQL Server Sparse Columns ?
We can define sparse columns by using either the CREATE TABLE or ALTER TABLE statement, using the SPARSE keyword for the columns that you require to be sparse columns.
- Sparse columns require more storage space for non-null values than a standard column.
- Data types like geography, geometry, image, ntext, text, and timestamp cannot be used for sparse columns.
Download Script File, which is used in Video Presentation for Demo.