SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sparse Columns


Sparse Columns

Author
Message
Saurabh Dwivedy
Saurabh Dwivedy
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 340
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
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14258 Visits: 12197
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
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15626 Visits: 11355
Lucky guess today. Thanks for making me think about it, though can't imagine it will ever be useful.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search