RSS

Estimate Table Size based on the Metadata from INFORMATION_SCHEMA.COLUMNS

24 Apr
Estimate Table Size based on the Metadata from INFORMATION_SCHEMA.COLUMNS

A problem I face often is not knowing the size of a new table that I’m creating. I put some basic code together that pulls the column information from INFORMATION_SCHEMA.COLUMNS and with some basic math gets the approximate size of the table based on the estimated number of rows.

The code is provided below. Change [DatabaseName] to your database name and update the set statements for the @tablename, @tableschema and @numberofrows variables as needed. Please remember this is just used for estimation. Varchar and NVarchar columns are difficult to calculate before hand because their size is based on the actual usage. So in other words for Varchar and NVarchar columns the size is an estimate using the maximum length as the norm for all the rows. In conclusion, you will get a ball park estimate of the possible maximum size of your table.

This solution depends on the fact that you have already created the table(s) in your database. I selected to most common data types, you will need to add any specific ones that are used in your environment that are not part of the code (i.e. Varchar(max)).

USE [DatabaseName]   /*change database name*/

DECLARE @tablename VARCHAR(300)
DECLARE @tableschema VARCHAR(300)
DECLARE @numberofrows INT

SET @tablename = 'TableName'  /*change table name*/
SET @tableschema= 'dbo'  /*change schema name*/
SET @numberofrows = 1000000;  /*change number of rows*/

WITH CalcTableSize
AS
(
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,
CASE
WHEN DATA_TYPE = 'bigint' THEN @numberofrows*8
WHEN DATA_TYPE = 'int' THEN @numberofrows*4
WHEN DATA_TYPE = 'smallint' THEN @numberofrows*2
WHEN DATA_TYPE = 'tinyint' THEN @numberofrows*2
WHEN DATA_TYPE = 'float' THEN @numberofrows*8
WHEN DATA_TYPE = 'real' THEN @numberofrows*4
WHEN DATA_TYPE = 'varchar' THEN (CHARACTER_MAXIMUM_LENGTH*@numberofrows) + 2
WHEN DATA_TYPE = 'nvarchar' THEN ((CHARACTER_MAXIMUM_LENGTH*@numberofrows) + 2)*2
/*The Calculation for varchar is based on the actual length of the data
entered which cannot be deduced from the table metadata the same applied for nvarchar*/
WHEN DATA_TYPE = 'char' THEN (CHARACTER_MAXIMUM_LENGTH*@numberofrows)
WHEN DATA_TYPE = 'nchar' THEN (CHARACTER_MAXIMUM_LENGTH*@numberofrows)*2
/*since it is Unicode it is double the size of a char*/
WHEN DATA_TYPE = 'bit' THEN @numberofrows*0.125
WHEN DATA_TYPE = 'date' THEN @numberofrows*3
WHEN DATA_TYPE = 'time' THEN @numberofrows*5
WHEN DATA_TYPE = 'datetime' THEN @numberofrows*8
WHEN DATA_TYPE = 'decimal' THEN
               CASE WHEN NUMERIC_PRECISION BETWEEN 1 AND 9 THEN @numberofrows*5
               WHEN NUMERIC_PRECISION BETWEEN 10 AND 19 THEN @numberofrows*9
               WHEN NUMERIC_PRECISION BETWEEN 20 AND 28 THEN @numberofrows*13
               WHEN NUMERIC_PRECISION BETWEEN 29 AND 38 THEN @numberofrows*17
               END
END AS SizeInBytes
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename
AND TABLE_SCHEMA = @tableschema
)

SELECT SUM(SizeInBytes)/1024/1024 AS SizeInMB,
SUM(SizeInBytes)/1024/1024/1024 AS SizeInGB
FROM CalcTableSize

Advertisements
 

Tags: , , , , ,

5 responses to “Estimate Table Size based on the Metadata from INFORMATION_SCHEMA.COLUMNS

  1. Muhammad Imran

    May 1, 2013 at 2:54 PM

    Hi Ayman,

    Nice script, But still there is a room to optimize the code using sys.types and STUFF.

    Thanks

    Imran

     
    • Ayman El-Ghazali

      May 1, 2013 at 3:02 PM

      Can you explain your concept a little more? sys.types has the generic lengths for the data types, but I’m getting the actual maximum lengths based on the table structure.

       
  2. Muhammad Imran

    May 1, 2013 at 3:36 PM

    For example : bigint=@rownumber * 8. so instead of this, we can pick up fixed length data from sys.types.

    Thank you.

     
    • Ayman El-Ghazali

      May 1, 2013 at 3:41 PM

      Ah ok, that makes sense instead of manually putting the size. Maybe I’ll update the code if they ever decide to change the data length in the next edition of SQL Server 🙂 Definitely, good advice though, thank you!

       
  3. Ayman El-Ghazali

    October 9, 2013 at 12:58 PM

    Tinyint should be 1 byte (I put *2 by accident… sorry)

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: