July 16, 2008 at 8:19 pm
Comments posted to this topic are about the item Sparse Columns
July 16, 2008 at 9:32 pm
Not convinced on the usefulness of sparse columns...
I think this is the reference URL the answer page should have sent you to (I got a resource not found error when I clicked on the link) ... http://msdn.microsoft.com/en-us/library/cc280604(SQL.100).aspx.
July 17, 2008 at 1:20 am
The documentation that this QotD is based on (thanks for the link, Simon - I did indeed get a resource not found error) is incorrect and misleading. I have just submitted a documentation bug for it, so hopefully it will get fixed before RTM.
The use of sparse column does reduce the number of bytes available for in-row storage. However, since SQL Server 2005 all varying length data can and will automatically be moved to LOB pages when the total row length exceeds the maximum of 8,060 or (if sparse columns are used) 8,018 bytes. As such, the limit on total row length is hard to calculate. If all columns are fixed length, it is indeed 8,018 bytes. But for varying length columns, only a pointer to the LOB storage has to remain in row; I think that requires two or four bytes. One can easily have 500 varchar(max) sparse columns, all filled with thousands of bytes, with no error at all.
The repro below shows a table with three sparse varchar(3000) columns, all populated to maximum length. No error is given when I run this, and the select shows that the data is stored just fine.
use tempdb
go
create table test
(pk int primary key,
sp1 varchar(3000) sparse,
sp2 varchar(3000) sparse,
sp3 varchar(3000) sparse);
go
insert into test (pk, sp1, sp2, sp3)
values (1, replicate('a', 3000), replicate('b', 3000), replicate('c', 3000))
go
select * from test
go
drop table test
go
Also note that I do get an error if I change all three sparse columns to char(3000) - but no error if I leave just one of them as varchar, nor if all three are char but one of the three is populated with NULL.
July 17, 2008 at 4:14 am
It would be nice to see some real-world examples where they would prove to be useful.
I suppose if the values in the table were generally null and only once in a while had info they could help performance?
I have to say it's a new feature that I'm not all that clear on.
July 17, 2008 at 5:48 am
Hugo Kornelis (7/17/2008)
...The use of sparse column does reduce the number of bytes available for in-row storage. However, since SQL Server 2005 all varying length data can and will automatically be moved to LOB pages when the total row length exceeds the maximum of 8,060 or (if sparse columns are used) 8,018 bytes. ...
Tnx for pointing that out Hugo, as I was quite surprised that the answer did not read "indeterminate", as I thought it should be.
Peter Rijs
BI Consultant, The Netherlands
July 17, 2008 at 6:47 am
When BOL discusses row size, it refers to in-row data, not the length of all the data in a record. So LOB storage is not included.
July 17, 2008 at 7:41 am
Steven Cameron (7/17/2008)
When BOL discusses row size, it refers to in-row data, not the length of all the data in a record. So LOB storage is not included.
If I would have thought about it a little bit more I would have figured out that what you are saying is correct. Instead I expected the answer to be indeterminate.
Intersting topic. Thanks for the question
July 17, 2008 at 7:54 am
I answered "Indeterminate", based on the same data that Hugo wrote up. I understand the point of the question and its answer, but this is a limited-case scenario.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2008 at 9:21 am
I also answered indeterminate (best guess since we don't have 2008 version). It seems counterintuitive that sparse data in columns would limit the number of rows available. Why wouldn't you just fill all null columns with " " instead so you could get more rows?:unsure:
July 17, 2008 at 9:28 am
The question has been edited to say in-row storage.
My opinion is that looking at LOB storage is storage outside the row, and it shouldn't be used in terms of calculating the maximum row size. It's nitpicky, and we base lots of our tuning decisions on rows that fit into a single 8kb page.
However, I see the point, and I have corrected the URL as well as awarded points back.
July 17, 2008 at 12:46 pm
Steve Jones - Editor (7/17/2008)
However, I see the point, and I have corrected the URL as well as awarded points back.
Thanks, Steve! 😀
August 26, 2008 at 12:01 pm
I have got this imp piece of info
In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.
they also reserve less space as well:)
September 9, 2008 at 1:00 pm
Hello, Guys
I will post several blogs at https://blogs.msdn.com/qingsongyao/default.aspx. Please visit my blog to see the examples of using sparse column.
December 7, 2010 at 1:55 pm
Steve Jones - SSC Editor (7/17/2008)
The question has been edited to say in-row storage.
I'm glad I didn't see this question until after it had been changed!
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply