Query for estimating the table or the database size - SQL Server 2014-2016

Question

Hi,
in order to request the right storage for a database, I need to estimate the related size before to feed it.
This database has many tables and each table has int columns, smallint columns, nvarchar columns, nchar columns and so on.
Does it exist any queries to estimate the size of a table or database?
Many thanks

Answer 1

Take the maximum bytes need per row, multiply it by the number of rows. Then you have an rough estimate.
This must include the bytes per row for each planned index. Fillfactor lesser than 1 will require more space.

Calculate this for each table.

Answer 2

You can try the following T-SQL. It is based on the sp_spaceused system stores procedure.

    USE [AdventureWorks2012];
    GO

    --// 5. Using sp_spaceused system SP
    exec sp_spaceused 'Person.Address';

    --// 6. Using DBCC CHECKTABLE function
    DBCC CHECKTABLE('Person.Address');

    --// Note: Before running 5 & 6 you may need to run this script:
    DBCC UPDATEUSAGE ('AdventureWorks2012','Person.Address') WITH COUNT_ROWS

-- // for the entire database
exec sp_spaceused;

Answer 3

I suggest you read this:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-database?view=sql-server-ver15

Answer 4

Hi @Pasquale Massimo Scorca ,

Please refer below:

1. If you only need to look at the overall usage of data files, DBCC SHOWFILESTATS is a better choice.
2. If you want to see the space usage of each object, you can use the dynamic management view sys.dm_db_partition_stats.
3. If you still want to know the usage and fragmentation of each page, each area, then DBCC SHOWCONTIG or DMV sys.dm_db_index_physical_stats is a better choice.

In your situation, you could consider to use DBCC SHOWFILESTATS to get the size of one database and use sys.dm_db_partition_stats or DBCC SHOWCONTIG(sys.dm_db_index_physical_stats) to get the size of one table.

USE AdventureWorks2016  
  
DBCC SHOWFILESTATS  
  
SELECT * FROM sys.dm_db_partition_stats     
WHERE object_id = OBJECT_ID('HumanResources.Employee');    
  
DBCC SHOWCONTIG ('HumanResources.Employee');    
  
Sp_spaceused 'HumanResources.Employee'  

You could also refer below link for more details about calculation:
SQL Server Table Size Estimator

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa