size

Question

Post reply

size

Mike Levan

SSChampion

Points: 14201
More actions
August 16, 2007 at 9:03 am

#125277

How can i measure the sizse of the rows.
say for example when i ran a query, it returend 49974 rows/records. I wud like to know the size of that rows in mega bytes.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Use this to calculate the row length in bytes (replace 'TableName' with your table name:

select sum(length) from syscolumns where id = object_id('TableName')

Greg

Mike Levan SSChampion Points: 14201 More actions · Answer 2

I want size for the no of rows returned.

if select * from emp where job=5

returns 5023 rows, I want the size of only these rows

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

Now that you know the row length, multipy it by the number of rows returned in the query.

Greg

Mike Levan SSChampion Points: 14201 More actions · Answer 4

row length?

I know the no of rows but how can i find row length for the rows returned from entire DB.

Mike Levan SSChampion Points: 14201 More actions · Answer 5

Total row length for all tables in my DB is 81245

and the row count for all tables in the same DB is 7234, now when i multiply these 2 i get more value than what i was expecting.

But the DB size shows as 348 MB.

If I multiply (81245 * 7234)/1024/1024 = 560 MB which more than my DB size. How is it possible?

David McFarland SSChampion Points: 11815 More actions · Answer 6

Because of the way you're calculating. The distribution of row lengths in various tables, as well as the distribution of row counts among the same tables renders your calculation useless. As an example (extremely simplified), if I have 2 tables, 1 with 1 row that is 8000 bytes long, as well as 1 table with 100 rows that are 4 bytes long, your method would return total row length of 8004 and row count of 101, giving a total of 808404 bytes, when in reality, it would be much closer to 8400 bytes.

I personally think you should take steve smith's advice in the other thread you have open on this subject, as it is a far more accurate, less painful method, if he is correct in surmising the reason behind all of this.

Mike Levan SSChampion Points: 14201 More actions · Answer 7

I am calculating row length for each table and then summing up for entire DB

David McFarland SSChampion Points: 11815 More actions · Answer 8

I'm aware of that, and that's the wrong method to use, as I demonstrated above with an example.

Lowell SSC Guru Points: 323518 More actions · Answer 9

this is similar to a different thread i was in, where we calculated the difference between the defined length,and the max used space for a row;

take a look here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=385732#bm386585

even if you multipled the MaxUsedLength by the number of rows in a table, you'd still be off though.

i think you need to take the datalength() of every field to get an actual number you are looking for, ie

select sum( datalength(column1),datalength(column2) ......etc

from sometable

and then sum THAT number up to get your total...

sounds like just na exercise in learning column names to me, because eventaully , the total will be what sp_spaceused or Enterprise manager reports as used space anyway, right?

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!