November 8, 2017 at 8:45 pm
Comments posted to this topic are about the item How Large is a Table?
November 8, 2017 at 10:05 pm
That is really interesting, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 8, 2017 at 10:32 pm
Insufficient info given on the explanation of the answer. You need to tell the people what those 7 bytes are.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2017 at 12:09 am
Good question thanks Steve.
...
November 9, 2017 at 3:18 am
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.
November 9, 2017 at 6:38 am
Jeff Moden - Wednesday, November 8, 2017 10:32 PMInsufficient 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
November 9, 2017 at 7:32 am
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
November 9, 2017 at 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.
November 9, 2017 at 7:52 am
George Vobr - Thursday, November 9, 2017 7:45 AMThanks 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
Change is inevitable... Change for the better is not.
November 10, 2017 at 9:04 pm
Jeff Moden - Thursday, November 9, 2017 7:52 AMGeorge Vobr - Thursday, November 9, 2017 7:45 AMThanks 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
November 13, 2017 at 3:59 am
TomThomson - Thursday, November 9, 2017 6:38 AMt'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