3

I am looking for an up to date tool to accurately calculate the total row size and page-density of any SQL table definition for SQL Server 2005+.

Please note that there are plenty of resources concerning calculating sizes of rows in existing tables, estimating techniques for sizing, etc... However, I am designing tables and have some options about column size which I am trying to balance with efficient data access - meaning that I can relocate less-frequently accessed long text into dedicated tables to allow the most frequent access of these new tables to operate at optimum speed.

Ideally there would be an online facility where a create statement can be cut and pasted, or a sproc I can run on a dev db.

tarzanbappa
  • 4,930
  • 22
  • 75
  • 117
user2874310
  • 61
  • 4

1 Answers1

1

and The answer is a simple one until you start making proper table design and balance that against joins and FK data and disk access.

I'd have a look an see how many data pages you are using and remember that one reads an extend (8 data pages) from disk, not only the data page you are looking for. Then there is the option for data compression in your table as well as sparse columns and out of row type of data storage and variable length characters.

It's not about how much data is in a column, it's really about how many data reads and CPU you need to get it. this you can test when executing a Query and looking against the ACTUAL QUERY PLAN.

As for space used you can use a stored procedure called sp_spaceused. here is a source you can use to see how one could use it in dbforms

Hope it helps

Walter

Community
  • 1
  • 1
Walter Verhoeven
  • 213
  • 1
  • 6
  • Walter I shall accept your answer as I read it as saying that 'it aint that simple' and that is a point of value to know. That said, IMHO it should still be possible to provide a calculation engine that would predict efficiency in ideal-world conditions. Thanks. – user2874310 Feb 19 '14 at 10:37