SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

Previously I wrote about SQL SERVER – 2008 – Introduction to SPARSE Columns. Let us understand the concept of SPARSE column in more detail. I suggest you read the first part before continuing reading this article.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

  • INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
  • SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
  • SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

  • SPARSE column does not have IDENTITY or ROWGUIDCOL property.
  • SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
  • SPARSE column can not have default value or rule or computed column.
  • Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
  • Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
  • A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

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

About these ads

24 thoughts on “SQL SERVER – 2008 – Introduction to SPARSE Columns – Part 2

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

  2. SPARSE column can not have default value or rule or computed column.

    Only this thing looks odd.Other things are not that much problem

    Like

  3. Sorry if this is a naive point!

    Now we have the final release of SQl 2008, I have been trying to create a table with more that 1024 columns. I cannot. Even if all columns are defined as ‘int sparse’ I still get a max 1024 col error message.

    Any advice would be very welcome.

    Like

  4. Hello, Richard
    In order to create more than 1024 columns, you have to have a columnset column defined in the table. We explict request this because the client can not retrieve more than 1024 columns, and we do not want people facing the issue of not being to retrieving data. Once the table has columnset defined, the select * will hide all sparse columns, and only see the column set. Neanwhile, people can still select individual sparse columns in their query. The default value of sparse column is NUll, that is the reason why we can not have default or rule on the column

    Like

  5. So I’ve created my wide table and I need to import a comma delimited file…and for the life of me, I cannot get it to import.

    I’ve used the import wizard (didn’t expect that to work) and bulk insert, all to no avail.

    Is there a trick to this?

    Like

  6. if you create a wide table (i.e., a table with column set). The default in/out method will only show the column set and hidden all sparse columns. If you want to import using the sparse columns, you may need to create a customized format file. Please refer the bcp format file for details.

    Like

  7. “A table operation which involves SPARSE column takes performance hit over regular column”

    Can you elaborate on what you mean by above? How severe is the performance impact?

    Like

  8. @mike, Honestly, I think it’s going to depend on a whole lot of things. Average size of the XML column that houses them all, but most importantly whether it’s part of any of the search arguments. If it’s just in the returned set I think you’re fine, but I wouldn’t use it if it’s in the WHERE clause.

    Like

  9. Sparse columns are incompatible with data compression. Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
    @Reference MSDN : “http://msdn.microsoft.com/en-us/library/cc280604.aspx”

    Like

  10. Sparse columns brokes cursor statements. Until you restart your database server !.
    Someone should write this issue in “Restrictions for Using Sparse Columns” section on MSDN library.

    Like

  11. Maybe I’m sleepy, but I don’t understand this at all:

    “Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.”

    What is a “clustered index key”? Do you mean a clustered index that supports a key? But the first sentence already says a SPARSE column cannot have a clustered index placed on it. Or do you mean that a column that is part of a key which is supported by a clustered index cannot be SPARSE? BOL just says:

    “A sparse column cannot be part of a clustered index or a unique primary key index.”

    Is what you’re saying the same thing? Or something different? Thanks.

    Like

  12. Hi ,
    I tried with the below statement i.e Inserted not null and null values alternatively . The table with sparse columns takes more space than tables having unsparced columns. Any idea on this behavior ?

    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

    DECLARE @FirstCol INT,
    @SecondCol VARCHAR(100),
    @ThirdCol SmallDateTime

    WHILE @idx < 150000
    BEGIN

    Select @FirstCol = Case when @idx%2 = 0 Then 1000
    Else Null End

    Select @SecondCol = Case when @idx%2 = 0 Then 'Value'
    Else Null End

    Select @ThirdCol = Case when @idx%2 = 0 Then GETDATE()
    Else Null End

    INSERT INTO UnSparsed VALUES (@FirstCol,@SecondCol, @ThirdCol)
    INSERT INTO Sparsed VALUES (@FirstCol, @SecondCol, @ThirdCol)
    SET @idx+=1
    END
    GO
    sp_spaceused 'UnSparsed'
    GO
    sp_spaceused 'Sparsed'
    GO
    DROP TABLE UnSparsed
    GO
    DROP TABLE Sparsed
    GO

    Here are the results

    name rows reserved data index_size unused
    UnSparsed 150000 4040 KB 3976 KB 8 KB 56 KB

    name rows reserved data index_size unused
    Sparsed 150000 4616 KB 4584 KB 8 KB 24 KB

    Like

  13. Ganesan, it’s all about ratios and percentages. Here’s the formula I came up with, based on trial and error. Yours isn’t a good test, since you always have the same length, and the ratio is 50%. Remember that the overhead is an extra 2 bytes for each field.

    Standard
    =(Number_Of_Rows*(Average_Varchar_Length+2)
    *((100-Percent_Null)/100))
    +(Number_Of_Rows*(Percent_Null/100*2))

    Sparse:
    =(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

    Like

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

  15. Its not simple as it appears to me, I tried it on my user table with 1000000 records but with no benefit although middlename column, featured column have more than 800000 null records(middlename, parentid ). Here is the details of how I did it. Am I not doing it correctly or missing somewhere, Please suggest if anyone has really used it in practise.
    Thanks.

    select top 400000 * into user_with_sparse from dbo.users

    select top 400000 * into user_without_sparse from dbo.users

    ALTER TABLE user_with_sparse ALTER COLUMN middlename varchar(40) SPARSE NULL

    ALTER TABLE user_with_sparse ALTER COLUMN Featured smallint SPARSE NULL

    sp_spaceused ‘user_with_sparse’
    user_with_sparse 400000 377120 KB 377032 KB 16 KB 72 KB

    sp_spaceused ‘user_without_sparse’
    user_without_sparse 400000 375280 KB 375184 KB 16 KB 80 KB

    Like

  16. Its not simple as it appears to me, I tried it on my user table with 1000000 records but with no benefit although middlename column, featured column have 900000 null records. Here is the details of how I did it. Am I not doing it correctly or missing somewhere, Please suggest if anyone has used it in practice.
    Thanks.

    select top 400000 * into user_with_sparse from dbo.users

    select top 400000 * into user_without_sparse from dbo.users

    ALTER TABLE user_with_sparse ALTER COLUMN middlename varchar(40) SPARSE NULL

    ALTER TABLE user_with_sparse ALTER COLUMN Featured smallint SPARSE NULL

    sp_spaceused ‘user_with_sparse’
    user_with_sparse 400000 377120 KB 377032 KB 16 KB 72 KB

    sp_spaceused ‘user_without_sparse’
    user_without_sparse 400000 375280 KB 375184 KB 16 KB 80 KB

    Like

  17. HI friends,

    as per theory in sql server we can create 1024 non sparse column and 100000 sparse columns but when i checked, we cant create more than 1024 column in sql server per table.

    also we would like to discuss, if we keep not null value in sparse column,it take same size as we store it into non sparse column. but as per theory it should take 4 byte extra. example the datetime take 8 byte to stored date in non sparse column. but as per theory it should take 12 byte(8+4) byte when we store it in sparse columns. but i dint find this practically. Please suggest me if i am wrong.

    Like

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