Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sparse Columns Expand / Collapse
Author
Message
Posted Friday, February 20, 2009 12:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 6:54 AM
Points: 150, Visits: 244
Comments posted to this topic are about the item Sparse Columns
Post #661071
Posted Friday, February 20, 2009 8:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, February 14, 2013 7:48 AM
Points: 2,647, Visits: 135
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
Post #661333
Posted Friday, February 20, 2009 9:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
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
Post #661469
Posted Friday, February 20, 2009 11:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 13,776, Visits: 28,178
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


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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #661551
Posted Friday, February 20, 2009 11:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:26 AM
Points: 31,078, Visits: 15,520
Answers changed, points awarded back.

Sorry







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #661560
Posted Friday, February 20, 2009 11:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 6:54 AM
Points: 150, Visits: 244
I'm sorry for this mistake.
Post #661565
Posted Friday, February 20, 2009 1:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
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%.
Post #661669
Posted Friday, February 20, 2009 2:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 2,580, Visits: 3,875
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.
Post #661765
Posted Friday, February 20, 2009 2:55 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
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
Post #661808
Posted Wednesday, March 31, 2010 5:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 7,736, Visits: 9,480
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

Me too - must be something in the beer.


Tom
Post #894289
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse