Determine Row Size on a Table

Question

Determine Row Size on a Table

fjmorales

Default port

Points: 1451
More actions
August 6, 2009 at 2:00 pm

#135185

Hi:
Does anybody know how to determine the size of each single row on a table?
Thansk for the help.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Do you mean the actual size of the stored data for each row, or do you mean the total possible size for the columns?

- 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

fjmorales Default port Points: 1451 More actions · Answer 2

The size of a row. I need this info to forecast the amount of space that certain table will occupied after X amount data inserts.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

fjmorales (8/6/2009)
The size of a row. I need this info to forecast the amount of space that certain table will occupied after X amount data inserts.

For real world capacity planning purposes you want to assume your table will be as good or bad organized/fragmented as it is today.

Having said that run:

EXEC sp_spaceused 'dbo.MyTable' ...

... return will be something like:

namerowsreserveddataindex_sizeunused

MyTable 329 272 KB136 KB96 KB40 KB

Just look at Data Space, Index Space and Row count; with that information at hand I'm sure you can infer space needed for whatever row population you are plainning for 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

Heh... guess what happens when you do a search for the following in Books Online? 😉

[font="Arial Black"]Estimating the Size of a Table [/font]

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

ps. One Orange Chip Points: 29422 More actions · Answer 5

Rows_Per_Page = 8096 / (Row_Size + 2)

I've always been confused abt this part of the overall equation. How exactly adding 2 to the number of rows helps in finding rows per page?

Refer :- http://msdn.microsoft.com/en-us/library/ms189124.aspx

Pradeep Singh

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 6

ps (8/6/2009)
Rows_Per_Page = 8096 / (Row_Size + 2)
I've always been confused abt this part of the overall equation. How exactly adding 2 to the number of rows helps in finding rows per page?
Refer :- http://msdn.microsoft.com/en-us/library/ms189124.aspx

It's not adding 2 to the number of rows... it's adding 2 to the row size.

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

Lowell SSC Guru Points: 323518 More actions · Answer 7

the extra two bytes are so they can store the physical address of the memory location of the row.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ps. One Orange Chip Points: 29422 More actions · Answer 8

ps.

One Orange Chip

Points: 29422

August 9, 2009 at 9:36 am

#1036487

Thanks Jeff and Lowell 🙂

Pradeep Singh

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 9

You bet... thanks for the feedback, Pradeep...

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