Finding Space occupied by a row

Question

Post reply

Finding Space occupied by a row

jsheikabdullah

SSC Veteran

Points: 276
More actions
November 2, 2009 at 9:24 pm

#211763

Can anyone pls help me on how to find the actual space occupied by a row in a table

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

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

PiMané SSChampion Points: 13965 More actions · Answer 1

you can use DATALENGTH function.

SELECT ISNULL(DATALENGTH ([COL0]),0) + ISNULL(DATALENGTH ([COL1]),0) + .... FROM

....

you can add the key column's at the begin to know witch row is...

see post http://www.sqlservercentral.com/Forums/Topic226699-5-1.aspx

Pedro

If you need to work better, try working less...

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

This is about the 3rd time this question has been asked in as many days... why do you need to know this?

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

Jeff Moden (11/3/2009)
This is about the 3rd time this question has been asked in as many days... why do you need to know this?

homework questions, you think? trying to make people use built in functions?

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!

PiMané SSChampion Points: 13965 More actions · Answer 4

Why the need to know this?

Easy, we have a CRM database and each customer pays the space they use. They pay a fixed fee that gives them xGB of info, over that they have to pay extra.

Pedro

If you need to work better, try working less...

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

What I think you'd want to do is calculate the sizes of all rows that you have in the db, for all tables, and give that to the customers as an estimate of space.

500 contacts = xx kb

200 phone numbers = yy kb

...

Then for the actual customer, are you going to go on actual bytes used by data or bytes used by the tables? They are two different things. varchars have some overhead against the row size, not much, but there is overhead. Fillfactor affects space use, you might lower is for resource intensive usage, etc.

I'd be careful about how you word this and charge people.

PiMané SSChampion Points: 13965 More actions · Answer 6

Can't do that "big picture" since our customers share the same tables on the same databases.

It's a very "heavy" process that runs every week, to give an average at the end of the month.

Besides DATALENGTH() is there any other function, like ROWSIZE() or something?! 😀 That would be nice :D...

Pedro

If you need to work better, try working less...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Ah... thanks for the feedback on what you actually need it for. It's a rare pleasure to get that kind of feedback.

I'm in the process of rebuilding my computer (obviously, I have the internet connection working again, finally) and I haven't reinstalled SQL Server yet so I can write some tested demo code BUT... why not use sp_SpaceUsed so you can include the total size of the table including indexes and the like, count the total number of rows and the number of rows for each customer and do the simple math from there?

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Or - use the reports and get all tables at the same time, broken down by type of space, (break it out by partition if you like), with an option to export the results to excel, etc...

Table Name# RecordsReserved (KB)Used (KB)

dbo.MattTest14,000,000266,672266,480

Index (PK__MattTest__4B5BD7F80519C6AF)4,000,000163,080163,056

Index (MattTest1_UCI) 4,000,000103,592103,424

dbo.tally1,000,00012,93612,920

Index (PK__tally__3BD019B300551192)1,000,00012,93612,920

dbo.tally21,000,00016,84816,824

Table 1,000,00016,84816,824

I know I know, not sexy in a command-line kind of way.....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?