Find Space occupied by a Row
-
Hi All,
Do we have any way to find out the space occupied by individual records in a table? My objective is to find out the space occupied by each record in a table where columns names can vary from int, float, varchar, nvarchar(max) etc.
For example, in below table, where content of both the records is very much different:
Thanks in advance.
Regards.
- T.Ashish wrote:
My objective is to find out the space occupied by each record in a table ...
To what end? What will such information be used for? I ask because the ultimate usage of such information may make a difference in how such a thing might be done.
--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) -
You can calculate this easily, using the datatypes and accounting for overhead. I believe varchar is 2 bytes overhead, and nvarchar is likely 2 or 4. It's in the docs somewhere.
However, I agree with Jeff. What's the point here?
-
and... if using compression space used will vary a lot depending on the compression type and on the data.
-
To add to Steve's comment, there is more overhead than just the dataype overhead on a row. On top of the overhead by VARCHAR and NVARCHAR, you also have the row header, null block, etc.
A good read on that is available here:
https://www.c-sharpcorner.com/article/calculate-data-row-space-usage-in-sql-server/
If you need the min, max, and average,
DBCC SHOWCONTIG('<table name>') WITH SHOW TABLERESULTSwill give you that.
If you need an estimate on row sizes, you can use DATALENGTH(<column name>) and sum up that value for every column. You'll be missing the overhead (both datatype and the SQL magic stuff), but will give you a rough estimate. Did that on a 3 column table I had and the table had 11 bytes of overhead. I figured this out by using the DATALENGTH calculation I suggested, grabbed the smallest value and compared that to the MIN value of the SHOWCONTIG command. Probably not 100% accurate, but got me a value that worked for getting the max value that matched up with the SHOWCONTIG results.
Would be a huge pain in the behind to make this work for all tables, but if you are only working with a single table, it gives you a rough idea...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Rather than using DBCC SHOWCONTIG, use sys.dm_db_index_physical_stats. The row header information is included as a part of the Min, Avg, and Max row sizes.
I'd still like to know why someone needs it for every row, though.
--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)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply