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 Thursday, March 4, 2010 8:32 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
Comments posted to this topic are about the item Sparse Columns

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
Post #877349
Posted Thursday, March 4, 2010 9:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
Good question .... learned something from it ...

Thanks


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #877361
Posted Thursday, March 4, 2010 10:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 17,708, Visits: 15,571
Thanks Saurabh, I learned something about sparse columns today.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #877369
Posted Friday, March 5, 2010 12:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Good question....learnt from it...tnx
Post #877427
Posted Friday, March 5, 2010 12:25 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, September 28, 2011 7:06 AM
Points: 151, Visits: 20
I can get clear idea for this.

Thanks,
Keyur Patel
Post #877432
Posted Friday, March 5, 2010 12:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 3,982, Visits: 5,228
Nice question about a seldom-used part of SQL Server.
Learned something new myself.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #877433
Posted Friday, March 5, 2010 3:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,045, Visits: 369
sorry for sounding stupid, but has anybody implemented sparse in his/her code ?

what was the reasoning behind.. is it really worth ?
Post #877511
Posted Friday, March 5, 2010 7:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:01 AM
Points: 3,941, Visits: 3,639
This is good information. Thanks for the question.
Post #877657
Posted Friday, March 5, 2010 9:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:10 PM
Points: 2,669, Visits: 781
Guess I am a bit confused by the question
A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.


I'm not sure how something is "defined on them", it may have had a meaning different than the one I read into it. It is clear that a Sparse Column cannot have a Filestream attribute.

There have been times in the past when my sparse sql skills prompted me to look at a Union statement when trying to compare two values that would have best been seen in a left join. The sparse column would have come in handy. It is a good topic. I wonder if we can just reword it slightly... maybe "Can a sparse column have a FileStream Attibute?"


Jamie
Post #877739
Posted Friday, March 5, 2010 7:04 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
ziangij (3/5/2010)
sorry for sounding stupid, but has anybody implemented sparse in his/her code ?

what was the reasoning behind.. is it really worth ?

Implementation of the sparse column makes sense only if the data in the column is, well, sparse. This is because it takes more space to store not null values in the sparse columns. For example, character types, such as char and varchar need twice as many bytes to store not null data if the column is defined as sparse. For those types only if 60% or more of all records are nulls then it makes sense to define the respective columns as sparse. For example, many databases storing consumer/customer/person records might end up having a column storing data for so-called address line 2 which will not have any data in the majority of records, which makes such column a good candidate to be defined as sparse.

Oleg
Post #878045
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse