What is the maximum allowable size of a row in SQL SERVER?

• If a table does not contain any sparse column, then the maximum allowable row size is 8060 bytes.
• However, if a table contains sparse columns, then the maximum allowable row size is 8018 bytes.

How is a column changed from sparse to nonsparse or nonsparse to sparse

• When a column changed from sparse to nonsparse or nonsparse to sparse, the storage format of the column is changed. This is accomplished by the SQL SERVER Database Engine using the following procedure:
• The SQL SERVER Database Engine adds a new column to the table in the new storage size and format.
• For each row in the table, the SQL SERVER Database Engine updates and copies the value stored in the old column to the new column.
• The SQL SERVER Database Engine then removes the old column from the table schema.
• It then rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

List down the limitations for using SPARSE Columns

• A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
• A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
• A sparse column cannot have a default value or bound to a rule.
• A computed column cannot be marked as SPARSE although it can contain a sparse column.
• A sparse column cannot be part of a clustered index or a unique primary key index.
• A sparse column cannot be used as a partition key of a clustered index or heap, but it can be used as the partition key of a nonclustered index.
• A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
• Sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
• When a non-sparse column is changed to a sparse column, the sparse column will consume more space for non-null values. When a row is close to the maximum row size limit, the operation can fail.
• If a table contains a sparse column, the maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes.

What are the datatypes which cannot be specified as SPARSE?

Given below are the data types which cannot be specifies as SPARSE:

• geography
• geometry
• image
• ntext
• text
• timestamp
• user-defined data types

Explain the COLUMNS_UPDATED function and its relation to Sparse Column

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.

Sparse Columns in SQL Server 2008

Sparse Columns are a new feature introduced in SQL Server 2008. Sparse columns are ordinary columns that have an optimized storage for NULL values. Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. Thus, they reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

Sparse columns should be used when the space saved is at least 20 percent to 40 percent. Sparse columns can be used with column sets (column sets are defined by using the CREATE TABLE or ALTER TABLE statements) and filtered indexes.

Example

CREATE TABLE DocumentStore

(in_doc_id int PRIMARY KEY,

vc_title varchar(200) NOT NULL,

vc_production_specification varchar(20) SPARSE NULL,

in_production_location smallint SPARSE NULL,

vc_marketing_survey_group varchar(20) SPARSE NULL);

GO

Characteristics of Sparse Columns

• The Sparse keyword is used by the SQL Server Database Engine to optimize the storage of values in that column. When the column value is NULL for any row, the values require no storage.
• A table having sparse columns has the same catalog views 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.
• Since sparse columns are a property of the storage layer, a SELECT…INTO statement does not copy over the sparse column property into a new table.