Estimating SQL Server Table Sizes
October 16, 2013 6 Comments
Microsoft gives guidance for calculating the estimated sizes of your tables and indexes at http://msdn.microsoft.com/en-us/library/ms175991.aspx. Performing such estimates is an important step in determining required server capacity.
Unfortunately, Microsoft’s guidance consists of long textual descriptions of the steps that need to be followed to perform the estimations. It takes some time to interpret the instructions given by Microsoft and perform the calculations.
With that in mind, I put together a spreadsheet to help me perform the calculations. To use the spreadsheet, I simply fill out the spreadsheet with the total number of rows in the table and details of the columns (data types and sizes) in the table and/or index. In my experience, the estimates provided by the spreadsheet are within 5-10% of the actual sizes of the tables/indexes, which has been good enough for my needs.
You can download the spreadsheet here.
To complete the spreadsheet, simply enter the requested data into the cells with black borders. For example, enter the expected number of rows in the table in the cell labeled “Rows in Table’”. Enter the number of columns in the table in the cell labeled “# of Columns”. Continue until you have specified all of the information about the table or index. The spreadsheet uses the information to calculate the total space needed for the table or index.
To help you get started, the spreadsheet includes sample data for a table that contains 1699488 rows and has six columns. This table has two integer columns (one of which is the primary key), two datetime columns, one tinyint column, and one nvarchar column. The average size of the data in the nvarchar column is 12 characters. Compare these table specifications to the sample data in the spreadsheet for an example of how to map the specifications for your table/index into the spreadsheet.
Note that while Microsoft provides guidance for estimating sizes of tables, clustered indexes, and non-clustered indexes, this spreadsheet only handles tables and clustered indexes. I originally intended to add the calculations for non-clustered indexes, but simply did not find the time. So, I am providing the spreadsheet as-is. It should, at the very least, provide a good starting point for estimating table and index sizes.