DiskSpace requirement for 1M records

SQL Server Administration
1

Hello Team,
‌I have a requirement where I need to see how much space is used per each record for each database and I would like to know if how much disk space is required for 1M records‌ to be inserted.
‌T‌hx,
‌

2

You should calculate the average row length for each table. Make sure you include indexes. Then figure out how many tables and how many records will 1M records be inserted into (i.e. 50000 header records, 950,000 detail records). Then do the math

3

If you already have a table with some sample data, you can use the sys.dm_db_index_physical_stats view to get the information. Example from here:

SELECT * FROM sys.dm_db_index_physical_stats  
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');

You can also calculate how much space each column would take based on the data type. See here for a handy table. If you have indexes on the table, they will take up additional space as well.

4

JamesK's approach looks good.

Create a table with the extra structure you intend to have. Then INSERT say 50K or 100K rows. Use that to estimate the total size of the table.

If you can use page compression, be sure to do that, and to include that in your calcs.

5

Thanks all for the reply,

Can you provide an example on how to do that?

6

Also create multiple versions of a table with different column data type sizes to see the difference in size?between varchar(50), nvarchar(50)

use db1
go
create table persons(Name varchar(50))

insert into persons
--sampling
select name from sys.objects

use db2
create table persons(Name nvarchar(50))
insert into persons
--sampling
select name from sys.objects


``