How Large is a Table?

Question

How Large is a Table?

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
November 8, 2017 at 8:45 pm

#75775

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72499 More actions · Answer 1

That is really interesting, thanks Steve

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

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

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)

HappyGeek SSCoach Points: 18726 More actions · Answer 3

HappyGeek

SSCoach

Points: 18726

November 9, 2017 at 12:09 am

#1967298

Good question thanks Steve.

...

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 4

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 5

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

david.gugg SSCertifiable Points: 6033 More actions · Answer 6

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.

[font="Tahoma"]Personal blog relating fishing to database administration:[/font]
[font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

George Vobr SSCrazy Eights Points: 9917 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

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)

TomThomson SSC Guru Points: 104773 More actions · Answer 9

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

Toreador SSChampion Points: 11358 More actions · Answer 10

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 😉