If you're curious how large a SQL Server table will get use the below script to estimate the size after updating the table name, estimated number of records, and the freespace in your database.
set nocount on
declare @freespace as numeric(4, 2)
declare @page_space_available as
bigint
declare @estimated_records_after_one_year as bigint
declare @table_name as sysname
declare @max_record_size as
bigint
declare @records_per_page as
bigint
declare @total_datapages as
bigint
declare @RID_size as bigint
/*******************************************************************/
/********update these variables to get
estimated space usage********/
/*******************************************************************/
/*****/set @freespace = 0.1 /*****/
/*****/set @estimated_records_after_one_year= 3000000 /*****/
/*****/set @table_name = 'TABLE NAME' /*****/
/*******************************************************************/
/*******************************************************************/
/*******************************************************************/
/*A datapage is 8192 bytes, but up to
132 bytes of that can be the header leaving just 8060 bytes for data*/
set @page_space_available =
8060 * (1.0 - @freespace)
if not exists (select 1 from sys.objects where name =
@table_name and type
= 'u')
raiserror ('Table does not exist.',
16, 1)
else
begin
if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 0)
begin
raiserror ('This table is a HEAP and space estimate could be off due to
forwarded records, you should consider adding a clustered index.', 16, 1)
end
if exists (select 1 from sys.columns c inner join sys.objects o on o.object_id = c.object_id and o.name = @table_name and o.type = 'u' and c.max_length = -1)
begin
raiserror ('This table contains datatypes which do not live on the
datapage - such as varchar(max) or xml - this estimate is not entirely valid
since those datatype could reach an infinite size.', 16, 1)
end
select @max_record_size =
sum(case c.max_length when -1 then 10000 else c.max_length end)
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
and o.name = @table_name
and o.type = 'u'
if @max_record_size >
@page_space_available
begin
select 'This table contains a
datatype which may not live on a datapage so these results should be taken with
a grain of salt' as message
set @max_record_size =
@page_space_available
end
set @records_per_page =
@page_space_available / @max_record_size
set @total_datapages =
@estimated_records_after_one_year /
@records_per_page
select (@total_datapages
* 8192) / (1048576) as estimated_table_space_used_in_mb
--If there is a clustered index then grab the size of it in
bytes
if exists (select 1 from sys.indexes si inner join sys.objects so on so.object_id = si.object_id where so.type = 'u' and so.name = @table_name and si.index_id = 1)
begin
select @RID_size = sum(sc.max_length)
from sys.indexes si
inner join sys.objects so on so.object_id = si.object_id
inner join sys.sysindexkeys sik on
sik.id = so.object_id and sik.indid = si.index_id
inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid
where so.type = 'u'
and so.name = @table_name
and si.index_id = 1
end
else
begin
select @RID_size = 16
/*setting this to 16 bytes if the table is a HEAP -
it's not necessarily 16 bytes but I think
thats a good estimage for the file
identifier, page number, and row number.*/
end
select sum(max_length) as estimated_index_space_used_in_mb
from (
select ((@estimated_records_after_one_year
/ (@page_space_available / (sum(sc.max_length) + @RID_size))) * 8192) / (1048576) as max_length -- include a
constant for nonclustered key value and a row locator
from sys.indexes si
inner join sys.objects so on so.object_id = si.object_id
inner join sys.sysindexkeys sik on
sik.id = so.object_id and sik.indid = si.index_id
inner join sys.columns sc on sc.object_id = so.object_id and sc.column_id = sik.colid
where so.type = 'u'
and so.name = @table_name
and si.index_id <> 1 --Do not include
the clustered index
and si.index_id <> 0 --Do not include
heaps
group by si.name) t
end
