February 6, 2010 at 6:27 am
HI,
Can any one explain me why non null values in Sparse column takes 4 bytes extra?
Regards
JIM
February 13, 2010 at 7:21 am
I'm going to make an assumption here, an educated guess if you might. This functionality is only available in 2008 and this is the 2005 forum so you can't get to upset if I am wrong 🙂
My belief is that the data in a sparse column is actually stored outside of the table and the extra 4 bytes provides a pointer back to the original record. This would make sense with why you can't use the sparse column as part of a clustered index.
In a normal table, the null value is not really null at all and still use space on the disk. In order to avoid this, the database engine would need to separate out the data for that column into a separate structure that would have essentially two columns, the pointer and the data. This would be like having a separate table with a one-to-one relationship to it's parent where the child table may or may not have a related record. The extra overhead that would be involved for the non-nulls would essentially be a lookup to the other entity for the pointer, and if exists, the read and return of the appropriate data.
February 16, 2010 at 3:31 pm
That's pretty close.
This post answers your question: http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/5155da82-61da-475e-8ee6-2a1187cb75c4
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply