4

I am trying to calculate how much space (Mb) this would occupy. In the database table there is 7 bit columns, 2 tiny int and 1 guid.

Trying to calculate the amount that 16 000 rows would occupies.

My line of thought was that 7 bit columns consume 1 byte, 2 tiny ints consumes 2 bytes and a guid consumes 16 bytes. Total of 19byte for one row in the table? That would mean 304000 bytes for 16 000 rows or ~0.3mbs us that correct? Is there a meta data byte as well?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Patrick
  • 5,442
  • 9
  • 53
  • 104

1 Answers1

6

There are several estimators out there which take away the donkey work

You have to take into account the Null bitmap which will be 3 bytes in this case + number of rows per page + row header + row version + pointers + all the stuff here:

Inside the Storage Engine: Anatomy of a record

Edit:

Your 19 bytes of actual data

  • has 11 bytes overhead
  • total 30 bytes per row
  • around 269 rows per page (8096 / 30)
  • requires 60 pages (16000 / 269)

  • around 490k space (60 x 8192)

  • a few KB for the index structure of the primary

gbn
  • 422,506
  • 82
  • 585
  • 676
  • So this page is still pretty accurate even for SQL server 2008 R2? http://support.microsoft.com/kb/827968 – Patrick Jan 06 '11 at 12:31
  • And word about the transaction log? – bernd_k Jan 06 '11 at 13:00
  • I just created such a table as a heap, and it came in at 0.477MB Data, 0.008MB index. Adding a clustered PK against the guid column gives 0.453MB data, 0.016MB index. So the answer is approximately correct :-) – Damien_The_Unbeliever Jan 06 '11 at 13:32