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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

26 thoughts on “SQL SERVER – 2008 – Introduction to SPARSE Columns

  1. Pingback: SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2 Journey to SQL Authority with Pinal Dave

  2. Hi…

    I am not quite sure if sparse columns are that great. I mean SQL2008 does come with an option to compress row data.

    How would this feature work combined with sparse Cols?

    And I dont think the cost of diskspace is that high these days. You could save a few bytes here and there, but what would you really gain? You can have more columns per row, but I see 1024 and think do we realy need more in a realtional DB? I can see more in a cube, but then most columns would store redundant data,and row compression should be very efficient.

    You mention a performance hit? How big is it? That would be much more interesting.

  3. The row compression and page compression do compress Null value. The compression ratio of sparse is usually less than the page compression. However, Sparse has less CPU overhead than row compress and page compression because it does not compress the not null value, but only ignore the Null values. I will post an example of comparing the storage size and the performance different for these two techniques.

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 6 Journey to SQL Authority with Pinal Dave

  5. I have a question regarding compression in sql servwer 2008. i saw a demo recently excel 2010 loads 100 million records from sql server db and on click of a column, sorting happens with in no time, can you tell me on this compression technique ?

  6. I love your site, so this is my small contribution to it.

    This could be written better: “it will be take lesser space then regular column”

    It probably should say, “it will take less space than a regular column”.

    Thanks!
    Clay

  7. Please see dis snap..when we insert any not null value dan its giving just opposite result…!!!!

    set nocount on
    DECLARE @idx INT = 0
    WHILE @idx < 1000
    BEGIN
    INSERT INTO UnSparsed VALUES (4,null, null)
    INSERT INTO Sparsed VALUES (4, null, null)
    SET @idx+=1
    END
    set nocount off
    GO
    sp_spaceused 'UnSparsed'
    GO
    sp_spaceused 'Sparsed'

  8. Please see sparse columns in both tables…its have advantage

    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 ,
    SecondCol VARCHAR(100) ,
    ThirdCol SmallDateTime SPARSE….!!!!!!!!!!!!!!!!!!1)
    GO
    set nocount on
    DECLARE @idx INT = 0
    WHILE @idx < 5000
    BEGIN
    INSERT INTO UnSparsed VALUES (4,'ff', null)
    INSERT INTO Sparsed VALUES (4, 'ff', null)
    SET @idx+=1
    END
    set nocount off
    GO
    sp_spaceused 'UnSparsed'
    GO
    sp_spaceused 'Sparsed'

  9. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31 Journey to SQLAuthority

  10. I’m trying to use the following code to dynamically create a temp table with 2 numeric columns for each input lot returned from the included query. The query returns 364 rows so the code should create 728 SPARSE columns. When I try to run this code with the SPARSE column definition, I get the error:Cannot create a row of size 9563 which is greater than the allowable maximum row size of 8060, but if I remove the SPARSE code, the table is created/updated fine. Why can’t I create the table with SPARSE columns?

    IF (SELECT OBJECT_ID(‘tempdb..#tmpVintage’)) IS NOT NULL
    DROP TABLE #tmpVintage

    CREATE TABLE #tmpVintage(VntSeq INTEGER NULL,
    VntVintage VARCHAR(4) NOT NULL,
    VntTranDate DATETIME NOT NULL,
    VntIssueRcpt CHAR(1) NOT NULL,
    VntLotKey INTEGER NOT NULL,
    VntPodKey INTEGER NOT NULL,
    VntPodCount TINYINT NOT NULL,
    VntTagKey INTEGER NOT NULL,
    VntTranQty DECIMAL(18,5) NOT NULL,
    VntTranUOM VARCHAR(2) NOT NULL,
    VntTranRunQty DECIMAL(18,5) NULL,
    VntQty DECIMAL(18,5) NULL,
    VntRun DECIMAL(18,5) NULL,
    VntPct DECIMAL(18,5) NULL,
    VntSparseCols XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

    — ADD THE COLUMNS FOR EACH INPUT LOT – TO BE USED FOR KEEPING RUNNING TOTALS BY INPUT LOT
    SELECT @SQL =
    STUFF((SELECT DISTINCT
    ‘ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL ‘
    FROM #TraceInputs
    WHERE triInputLotKey 0
    ORDER BY ‘ALTER TABLE #tmpVintage ADD [VntRun' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL, [VntPct' + CAST(triInputLotKey AS VARCHAR) + '] DECIMAL(18,5) SPARSE NULL ‘
    FOR XML PATH(”)
    ), 1, 0, ”)
    EXECUTE (@SQL)

  11. Hi Pinal,

    Sparse column with zero value is taking space similar to non-null value.Do we have an advantage of using zeroes for sparse columns?

    Thanks & Regards,
    Sreelekha

  12. Hi – what is the difference between a null in a varchar and a null in a sparse varchar field. The varchar has 2 bytes of info and no space for the null, correct?

    Thanks!

  13. To shefalee –
    You are getting an error because you have executed in MS SQL Server 2005. you try in 2008, surely it will work.

  14. Hello sir,
    if we insert ‘NULL’ then SPARSE working fine, but if we insert 0 then it’s take more space compare to notsparse columns .
    plz tell me which one better

    wait your response

  15. Hi Pinal,

    I have seen the below code in so many examples, you are using for insert # records

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

    instead of the above code we can use below code also – ct?

    INSERT INTO Sparsed VALUES (NULL,NULL, NULL)
    Go 50000

    if it's correct, may I know is there any difference between the above two quries..?

    Thanks,
    Venkat_t919

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  18. 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……..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s