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.

  • I am not aware of any tools that do this for you. When in school waaaaay back when I used to do this with a spreadsheet. Each data type had a bit value and identifier and there was a rowCount cell. I could create estimates by plugging in a data type identifier and the spreadsheet would auto calculate max number of bits/byes required. This doesn't take too much time to set up and could easily be modified to give you min and max values. Oct 31 '14 at 20:10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.