Post reply

Sparse Columns

  • February 20, 2009 at 12:05 am

    #200502

    Comments posted to this topic are about the item Sparse Columns

  • February 20, 2009 at 8:10 am

    #947534

    Sparse columns REDUCE the space needed not negates: Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

    http://msdn.microsoft.com/en-us/library/cc280604.aspx

  • February 20, 2009 at 9:58 am

    #947652

    note to self: don't take quizzes till fully caffenated.. could have sworn I was ticking a check box that said "CANNOT have a default value... grumble :angry:

  • February 20, 2009 at 11:18 am

    #947726

    Lisa Phillip (2/20/2009)

    Sparse columns REDUCE the space needed not negates: Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

    http://msdn.microsoft.com/en-us/library/cc280604.aspx%5B/quote%5D

    Yep. I got caught by this one too. The answer is incorrect.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 20, 2009 at 11:27 am

    #947731

    Answers changed, points awarded back.

    Sorry

  • February 20, 2009 at 11:30 am

    #947735

    I'm sorry for this mistake.

  • February 20, 2009 at 1:07 pm

    #947834

    There are several sources from MS or MS related that specifically say storing a null takes NO space

    http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx

    http://msdn.microsoft.com/en-us/library/cc280604.aspx

    Specifically from the link above, a little aways below the 'less space' item quoted in the thread above, you find this (emphasis added)

    The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.

    So if you read the details, the 'reduction' for storing null values is apparently 100% 'eliminate' would perhaps have been a more accurate word for them to use than 'reduce', since most of us think of reduce as being less than a 100% modification.. Still since it's proper to speak of 'reducing a price to zero' I believe it's technically grammatically correct to say 'reduce' in that instance, and NOT a conflict to then later in the same document spell out the extent of the reduction being 100%.

  • February 20, 2009 at 2:14 pm

    #947923

    The sparse column itself requires no space for storage if it contains a null value. However, additional storage space is required to store non-null values in the sparse column. That's why you don't get a 100% total space savings. In some cases the savings is pretty low.

    http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx

    Please correct me if I'm wrong.

    The weekend is here... nice. 😎

  • February 20, 2009 at 2:55 pm

    #947965

    skjoldtc (2/20/2009)

    The sparse column itself requires no space for storage if it contains a null value. However, additional storage space is required to store non-null values in the sparse column. That's why you don't get a 100% total space savings. In some cases the savings is pretty low.

    http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx

    Please correct me if I'm wrong.

    The weekend is here... nice. 😎

    That's exactly as I understand it. The books-online stuff actually gives the approximate level of 'sparseness' (as it were) needed to achieve a 40% space savings.. So yeah a 'sparse' column that isn't actually sparsely populated with cells that contain non-null values, could very easily use more space.

    I think the question however was as to the tickbox for the item that said that storing a null in a sparse column uses zero space, which based on the docs referenced above is TRUE

  • March 31, 2010 at 5:24 pm

    #1143552

    SQAPro (2/20/2009)

    note to self: don't take quizzes till fully caffenated.. could have sworn I was ticking a check box that said "CANNOT have a default value... grumble :angry:

    Me too - must be something in the beer.

    Tom

  • November 2, 2012 at 3:10 am

    #1555341

    the same mistake even i did:( lost the point....but a good lesson...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply