Determine the byte size of a particular row?

[Logan1337]

Hi, I'm wondering if anyone knows how to determine the physical size of a particular row in a table. I suppose I could figure this out manually by determining the length of all fields, but was wondering if there's a built-in way to do this.

I'm actually using SQL Server Compact, but am interested in whether it's possible on any platform.

Thanks.

P.S.
I realize there are additional things like index entries that take up space as well. Ideally, I would like to figure out how much space could be reclaimed if a particular row were to be deleted... for capacity management.

[ck9663]

I know there's a posting of similar question before. You might want to search that. I can't find my previous reference.

Or you can try this:

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

-- CK

[Logan1337]

It appears this method only returns the maximum size of a column, not how much space a particular row is actually using in that column... unless I'm way off and unused space in a varchar is actually wasted.

Is that the case? If I have a nvarchar(4000) column, and store a string of 10 characters, will there be 3990 bytes wasted in the database file for that row? I was kind of under the assumption that things would be a little more optimized.

[ck9663]

No. Then you have to run through each record and each field since each one varies. And also be careful with CHAR vs VARCHAR and numeric field types.

-- CK

[Logan1337]

Ok thanks, I didn't think so but was worried there for a moment.

I think I've decided the best bet is to just store a "size" column in my table(s) and compute the length of the data in code before I insert/update the row. This will give me a rough idea of how much space a given row will use, at least compared to other rows.