Sparse Columns

  • Comments posted to this topic are about the item Sparse Columns

  • 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.

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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.

  • 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

  • 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.

  • 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

  • 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

  • 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:

  • 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.

  • 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! 😀


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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:)

  • 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.

  • 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