Friday 28 September 2012

Sparse Columns



Introductions

In this article I am trying to discuss about a very interesting feture of MS SQL Server and it is called the Sparse Columns. This feature is started from MS SQL Server 2008 and onwards. Hope it will be interesting and informative.

Point in Focus

What is Sparse columns

Understand the Sparse columns with Example

Which columns not support it

Which datatypes not support it.

Where it is Best use

Others restrictions

What is Sparse columns

Sparse columns are nothing but a ordinary columns which is designed for optimized the NULL values. The benefits of the Sparse columns is they reduce space required for NULL values.
Another benefits is its reduce the costs of increased processing overhead to retrive the non-null values.

Let see the MSDN defination related to Sparse columns.

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 nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent


In MS SQL Server 2008 the maximum columns supported by a table objects is 1024. But the Sparse columns does not comes under this limit. The maximum Sparse coum supported by a table is 100,000. So a table objects may conatins 1024 regular columns + 100,000 Sparse columns.

Understand the Sparse columns with Example

In this example I am using a system stored procedure sp_spaceused. Before staring the example a samll note related to sp_spaceused from MSDN is mentioned bellow. For more details, please follow the MSDN link.

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.


Step-1 [ Creating the Base Table ]

-- Without SPARSE Columns
CREATE TABLE ExmapleTable1
      (EMPID   INT IDENTITY(1,1),
       FNAME   VARCHAR(50),
       LNAME   VARCHAR(50));

GO
-- With SPARSE
CREATE TABLE ExmapleTable2
      (EMPID   INT IDENTITY(1,1),
       FNAME   VARCHAR(50) SPARSE,
       LNAME   VARCHAR(50) SPARSE);

Step-2 [ Now Insert Some Records ]

DECLARE @idx INT = 0;
WHILE @idx < 50000
      BEGIN
         INSERT INTO ExmapleTable1 VALUES (NULL, NULL);
         INSERT INTO ExmapleTable2 VALUES (NULL, NULL);
         SET @idx+=1;
      END

Step-3 [ Now compre both ]

sp_spaceused 'ExmapleTable1'
GO
sp_spaceused 'ExmapleTable2'


Name
Rows
Reserved
Data
index_size
Unused
ExmapleTable1
50000
2824 KB
2808 KB
8 KB
8 KB
Name
Rows
Reserved
Data
index_size
Unused
ExmapleTable2
50000
1416 KB
1408 KB
8 KB
8 KB
                                                           
Which columns not support it

Some of the columns mentioed bellow is not supported by Sparse
Computed Columns / RowGuid / Filestream / Identity / XML

Which datatypes not support it

The following datatype not supported Sparse
Geography / Geometry / Image / Ntext / Text / Timestamp / Userdefine data type

Where it is Best use

The Sparse columns is best used in Fintered Index. Where data are filtered in the row.

Others restrictions


We can not bound rueles with sparse columns and it can not have any defualt value.
It can not be a part of Clustered or a unique Primary key indexes. It can not be used as a partition key of a clustered index or heap.

Sparse columns can not be used with userdefine table type (Table variable and Table valued parameters)



Hope you like it.




Posted By: MR. JOYDEEP DAS

No comments:

Post a Comment