July 20, 2009 at 1:00 pm
I have been trying to figure out how much space each row in a particular table will use, I have been able to find storage sizes for int, small int, datetime, varchar, etc. But not for text. I know the absolute max is 2,147,483,647 bytes but just not sure on calculating actual so I was wondering if anyone could point me to where I can find the minimum space used by a text column, and how to calculate the actual space used. Is it like a varchar where it's the length + 2bytes, or 0 if its null?
July 20, 2009 at 1:16 pm
For an NULL row it will take probably 1-2 bytes per record to record the NULL, for a non-null row it will take at LEAST 16bytes for the pointer + space for the text on another page (unless you have the text on page option set, which for 7 and 2000 may not be available).
Help?
CEWII
July 20, 2009 at 1:39 pm
Okay so then for calculating how much space a non-null record will take it would be 16bytes + len of the text field as a rough estimate?
the point of all this is for an application we use we allow people to "delete" records but that just amounts to setting an 'isDeleted' flag to 1. But I have been asked to find how much space people are using, not including records that are deleted.
I can test with a null text field being 2 bytes, and a non-null being 16bytes + lenght, and see if that gets me to about the right size for the table while including deleted records.
July 20, 2009 at 3:08 pm
so even using everything i have found for the amount of space different data types use still was not getting very accurate results. Was some where around 80% of the size I should have been seeing.
So I tried looking for an average size for rows in this table. I have over 1400 databases with this table, so a decent sample. I end up with an average of about 16KB per row, but a standard deviation of 11.83. :ermm: So that ends up as being less useful then my original attempt.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply