Sparse Columns

Question

Post reply

Sparse Columns

Saurabh Dwivedy

SSCommitted

Points: 1887
More actions
March 4, 2010 at 8:32 pm

#217489

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!
March 5, 2010 at 12:25 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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[/url]
Before posting a performance problem please read[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks Saurabh, I learned something about sparse columns today.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jshailendra SSCrazy Points: 2260 More actions · Answer 3

jshailendra

SSCrazy

Points: 2260

March 5, 2010 at 12:13 am

#1129003

Good question....learnt from it...tnx

keyur.patel SSC Enthusiast Points: 189 More actions · Answer 4

keyur.patel

SSC Enthusiast

Points: 189

March 5, 2010 at 12:25 am

#1129007

I can get clear idea for this.

Thanks,

Keyur Patel

ziangij SSCrazy Eights Points: 8066 More actions · Answer 5

sorry for sounding stupid, but has anybody implemented sparse in his/her code ?

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

Cliff Jones SSChampion Points: 10547 More actions · Answer 6

Cliff Jones

SSChampion

Points: 10547

March 5, 2010 at 7:52 am

#1129185

This is good information. Thanks for the question.

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 7

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

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

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 9

Oleg Netchaev (3/5/2010)
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

Thanks Oleg for providing a relevant example. Appreciate it.

@jamie - You've got a point there. I think the wording could have been more precise. I am studying for the SQL Server MCTS exam and this topic (on sparse columns) came up somewhere. That's how I came up with the question. Not everything will be relevant to everyone. There are some who just like to learn something for the sheer academic pleasure the learning provides. For some it could turn out into a helpful idea to be implemented in a crux situation, perhaps.

Truthfully, I do SQL Server only because I like the subject matter and want to learn it. I have never used Sparse Columns in my actual experience.

This was my first attempt at posting a question on this wonderful forum. I hope to continue learning and wish all of you the same.

Regards

Saurabh

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!

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Nice question. I hadn't a clue, so today I made time to read all the BOL stuff about FILESTREAM, so today I have learnt more than most days.

Can't imagine why MS decided that columns with filestream values couldn't be sparse, though.

Tom

Paul White SSC Guru Points: 150467 More actions · Answer 11

Lucky guess today. Thanks for making me think about it, though can't imagine it will ever be useful.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi