SQL SERVER – 2008 – Introduction to SPARSE Columns

I have been writing recently about how SQL Server 2008 is better in terms of Data Stage and Backup Management. I have received very good replies from many users and have requested to write more about it. Today we will look into another interesting concept of SPARSE column. The reason I like this feature because it is way better in terms of how columns are managed in SQL Server.

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

Let us see following example of how SPARSE column saves space in database table.
CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,
SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)
GO
DECLARE @idx INT = 0
WHILE @idx < 50000
BEGIN
INSERT INTO
UnSparsed VALUES (NULL,NULL, NULL)
INSERT INTO Sparsed VALUES (NULL, NULL, NULL)
SET @idx+=1
END
GO
sp_spaceused 'UnSparsed'
GO
sp_spaceused 'Sparsed'
GO
DROP TABLE UnSparsed
GO
DROP TABLE Sparsed
GO

SQL SERVER - 2008 - Introduction to SPARSE Columns sparse

UPDATE : Read Advantages and Disadvantages of SPARSE Column SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2008 – Two Convenient Features Inline Assignment – Inline Operations
Next Post
SQL SERVER – 2005 – Two Important Security Update

Related Posts

27 Comments. Leave new

  • Hi

    This is Parvez

    I have a Table in which there are four columns
    I should get the output like this

    Fname Lastname Gender Username Password
    parvez shaik male paikm mkiap

    first two letters of 1 column & last two letters of 2 column and first letter in 3 column should be my username and it’s reverse should be my password

    Please help……..

    Reply
  • Hi,

    This is Sagar

    I got the metadata to create the wide table via an XML file and I had to make quite a lot of modifications to create the table.
    I need to load this wide table with 14000 columns out of which over 13000 columns are sparse.
    The source is a flat file which does not have column headings.

    I have tried the SSIS approach but to no avail. Also I tried BCP which doesnt work either.

    Any idea on how to load this ridiculous table.

    Thanks in Advance.

    Reply
  • Hi Pinal,

    I updated the 25000 rows in both the tables(Sparsed, Unsparsed) with the integer values (firstcol, secondcol)
    After updating sparsed table is taking more space than the unsparsed table. Please clarify.
    Thanks in advance.

    Reply
  • Hi,
    If I have replaced insert statements of your code as follows:

    INSERT INTO UnSparsed VALUES (NULL,’abcd’, NULL)
    INSERT INTO Sparsed VALUES (NULL, ‘abcd’, NULL)

    or
    INSERT INTO UnSparsed VALUES (1,null, NULL)
    INSERT INTO Sparsed VALUES (1, null, NULL)

    I got the same result in both cases.

    Summary, sparse is helpful when all columns are null.

    Thanks
    Md. Ismail

    Reply
  • Rasik Bihari Tiwari
    September 21, 2016 9:25 am

    There is a concept of wide tables which support 30K columns in a SQL Server table using the concept of column sets. Do they somehow relate to Sparse columns or that’s a complete different concept?

    Reply

Leave a Reply

Menu