I am attempting to determine if anyone has built a handy utility to query a set of fields and estimate a range of expected data usage of the set of fields.
I have looked at the Microsoft Article describing the process, as well as saw this other similar question but it just describes a generic process.
What I am looking for is query to tell me the estimated actual field usage requirements (minimum, medium, maximum) of a particular fields based on how they are actually utilized (i.e. is only 5 bytes of a 12 byte varchar in use) and taking into consideration the data type vs. another set of fields.
That is, if I have Fields A+B+E+G+Z2 it will requires between 9232 and 12344 space for 1 million rows, but if I do A+B+E+G it will only require 7212 to 9632 space for 1 million rows based on the actual usage of the existing fields.
The reason I am asking is I am trying to easily determine data storage requirements and retention for existing fields and weighing them against their usefulness for business purposes. Yes you can argue storage is cheap but I am looking for a utility query to perform exactly this.
At this point I do not care so much about the index size. In SQL 2008 I cannot find a utility to do this, and the closest thing I can get to it is the Top Tables Report for the entire table and all fields.
Best Answer
Perhaps this is a good start - You'd have to calculate: each data type times expected row count. Of course this is approximate result due to format/and possibly padding overhead, but might be a start.