One of the major enhancement in database engine of SQL Server 2008 is Sparse column. It improve data retrival and reduce the storage cost. It also can be used with Filtered Index to improve the performance.
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.
How to Create sparse column.
Simple , just mention sparse keyword in table creation or alter statement.
CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)
Note : There are many limitations as far as the implementation is concerned. like the column has to be null, cannot be included in Clustered index, merge replication,compression etc. Please refer the site mentioned above or BOL.
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.
How to Create sparse column.
Simple , just mention sparse keyword in table creation or alter statement.
CREATE TABLE TestSparseColumn
(Comments varchar(max) SPARSE null)
Note : There are many limitations as far as the implementation is concerned. like the column has to be null, cannot be included in Clustered index, merge replication,compression etc. Please refer the site mentioned above or BOL.
No comments:
Post a Comment