How Large is a Table?

  • Comments posted to this topic are about the item How Large is a Table?

  • That is really interesting, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Insufficient info given on the explanation of the answer.  You need to tell the people what those 7 bytes are.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Good question thanks Steve.

    ...

  • And... even better, the error message is clear and helpful. This is a lot better than the only vaguely useful "cannot convert varchar to int" message issued when you try to insert values for lots of columns into a table, for instance.

  • Jeff Moden - Wednesday, November 8, 2017 10:32 PM

    Insufficient info given on the explanation of the answer.  You need to tell the people what those 7 bytes are.

    Yes, it would improve the explanation to include that information.  I once knew what it was, but now I can't remember. I can remember "at least 7" as it's documented so I've read it, but I'd like to be able to calculate the total overhead per page from the table definition (which I could a very long time ago with SQL Server 2000 and more more recently with SQL Server 2008 R2) but only recall "at least" statements for recent versions of SQL Server (it's a pity useful information keeps disappearing from BoL).

    But even without that extra explanation this queston and answer may teach a lot of people something useful, because to date more answers have been wrong (134) than right (107) so far.  I would have expected this to have about 90% correct answers, not 44%. It's quite surprising that so many people either don't  know the row size limit or can't do simple arithmetic.

    Tom

  • I would like to see a reference in the answer as well.  I found two different Microsoft documentation pages and neither one mentioned a 7 byte overhead.

    Be still, and know that I am God - Psalm 46:10

  • Thanks for this question. I also learned how SQL Server 2016 memory-optimized tables support off-row storage.
    I' ve found that 7 bytes overhead is well described in the  Microsoft SQL Server 2014 Unleashed book.

  • George Vobr - Thursday, November 9, 2017 7:45 AM

    Thanks for this question. I also learned how SQL Server 2016 memory-optimized tables support off-row storage.
    I' ve found that 7 bytes overhead is well described in the  Microsoft SQL Server 2014 Unleashed book.

    I found it in the 2012 version of that book, as well.  It also describes the bits of some of the bytes involved.  And, just for anyone interested, the number of bytes used isn't limited to 7.  There will be more if variable length columns are used or the table has lots of columns.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, November 9, 2017 7:52 AM

    George Vobr - Thursday, November 9, 2017 7:45 AM

    Thanks for this question. I also learned how SQL Server 2016 memory-optimized tables support off-row storage.
    I' ve found that 7 bytes overhead is well described in the  Microsoft SQL Server 2014 Unleashed book.

    I found it in the 2012 version of that book, as well.  It also describes the bits of some of the bytes involved.  And, just for anyone interested, the number of bytes used isn't limited to 7.  There will be more if variable length columns are used or the table has lots of columns.

     Yes, the number of columns overhead seems to be 6+ceiling(N/4000)+ 2*ceiling(V/4096)+2V per row where N is the number of columns and V is how many of those N are variable length columns.

    Tom

  • TomThomson - Thursday, November 9, 2017 6:38 AM

    t's quite surprising that so many people either don't  know the row size limit or can't do simple arithmetic.

    Or in my case, didn't read the question properly and assumed varchar rather than char 😉

Viewing 11 posts - 1 through 10 (of 10 total)

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