Estimating the table size...
Well.... it's bit lengthy; I have used 6 functions to get the table size. Table size returned is in KB.
- fn_CalculateHeapSize: This function calculates the size of heap i.e when no clustered index has been defined.
- fn_ClusteredIndexSize: This function calculate the clustered index size.
- fn_GetNonClusteredIndexSize: This function calculate the size of all non-clustered index.
- fn_GetTableSize: This function combines the result of above functions to give the final results.
- fn_getIndexSpace: A common function used for calculation in above functions.
- fn_GetLeafLevelIndexSpace: A common function used for calculation in above functions.
- Usage: select dbo.fn_GetTableSize('tablename')
Note:
I have not considered the LOB cols. in my calculations; also this is an estimation of a table size as given in BOL "Estimating the table size"
CREATE function [dbo].[fn_CalculateHeapSize]
(@Tablename varchar(100))
Returns varchar(100)
AS
Begin
-- Calculate the space used taken at leaf level
Declare @Num_Rows float,@Num_Cols int,@Fixed_data_size int,@Num_var_Cols int,@Max_var_size int
Declare @Null_Bitmap int,@Variable_Data_Size int,@Heap_size bigint
Declare @Row_Size int,@Rows_per_page float,@Num_Pages float
set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@Tablename))
set @Num_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename))
set @Fixed_data_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id not in (165,167,231,34,35,99))
set @Num_var_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id in (165,167,231,34,35,99))
set @Max_var_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id in (165,167,231,34,35,99))
set @Null_Bitmap= 2 + (@Num_Cols + 7)/8
If( @Num_var_Cols = 0)
BEGIN
set @Variable_Data_Size = 0
END
ELSE
begin
set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
END
set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info
set @Rows_per_page= 8096/(@Row_Size + 2)
-- No. of pages needed to store rows
set @Num_Pages= ceiling(@Num_Rows/@Rows_per_page)
set @Heap_size = (8192 * @Num_Pages)/1024
-- Space used to store index info
return Ltrim(str(@Heap_size))-- + ' KB'
End
CREATE function [dbo].[fn_ClusteredIndexSize]
(@Tablename varchar(100))
Returns BigInt
AS
Begin
-- Calculate the space used taken at leaf level
Declare @Num_Rows float,@Num_Cols int,@Fixed_data_size int,@Num_var_Cols int,@Max_var_size int,@fill_factor int
Declare @uniquifier smallint,@uniquefiersize smallint,@Null_Bitmap int,@Variable_Data_Size int,@Total_Space varchar(100)
Declare @Row_Size int,@Rows_per_page float,@Free_rows_per_page float,@level float ,@Num_Pages float,@Leaf_level_space int
set @uniquifier=1
set @uniquefiersize=4
set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@Tablename))
set @Num_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename))
set @Fixed_data_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id not in (165,167,231,34,35,99))
set @Num_var_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id in (165,167,231,34,35,99))
set @Max_var_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename)
and system_type_id in (165,167,231,34,35,99))
If ( (select is_unique from sys.indexes where type=1 and object_id=Object_id(@Tablename)) = 0 )
Begin
set @Num_Cols = @Num_Cols + @uniquifier
set @Num_var_Cols = @Num_var_Cols + @uniquifier
set @Max_var_size = @Max_var_size + @uniquefiersize
End
set @Null_Bitmap= 2 + (@Num_Cols + 7)/8
set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info
set @Rows_per_page= 8096/(@Row_Size + 2)
set @fill_factor=(select fill_factor from sys.indexes where object_id=object_id(@Tablename) and type =1)
-- No. of reserved free rows per page
set @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100) / (@Row_Size + 2))
-- No. of pages needed to store rows
set @Num_Pages= ceiling((@Num_Rows/(@Rows_per_page - @Free_rows_per_page)))
set @Leaf_level_space = 8192 * @Num_Pages
-- Space used to store index info
Declare @Num_Key_cols int,@Fixed_key_size int,@Num_var_key_cols int,@Max_var_key_size int
Declare @Index_Null_Bitmap int,@Variable_Key_size int,@Index_row_size int,@Index_row_per_page float,@levels int
Declare @Num_Index_pages int,@Index_level_space int,@Null_Cols int
Set @Num_Key_cols=(select Keycnt from sys.sysindexes where id=object_id(@Tablename) and indid=1)
Set @Fixed_key_size = (select sum(max_length) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id
and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
and c.object_id=b.object_id and a.column_id=c.column_id and system_type_id not in (165,167,231,34,35,99))
set @Num_var_key_cols = (select count(c.name) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id
and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
and c.object_id=b.object_id and a.column_id=c.column_id and
system_type_id in (165,167,231,34,35,99))
set @Max_var_key_size = (select IsNull(sum(max_length),0) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id
and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
and c.object_id=b.object_id and a.column_id=c.column_id and
system_type_id in (165,167,231,34,35,99))
If ( (select is_unique from sys.indexes where type=1 and object_id=Object_id(@Tablename)) = 0 )
Begin
set @Num_Key_cols = @Num_Key_cols + @uniquifier
set @Num_var_key_cols = @Num_var_key_cols + @uniquifier
set @Max_var_key_size = @Max_var_key_size + @uniquefiersize
End
set @Null_Cols=(select IsNull(count(c.name),0) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id
and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
and c.object_id=b.object_id and a.column_id=c.column_id and c.is_nullable=1)
select @Index_level_space=dbo.fn_getIndexSpace(@Null_Cols,
@Num_var_key_cols,
@Max_var_key_size,
@Fixed_key_size,
@Num_Rows)
set @Total_space = Ltrim(str((@Index_level_space+@Leaf_level_space)/(1024)))
return @Total_space
End
CREATE Function [dbo].[fn_getIndexSpace]
( @Num_Null_key_cols int,@Num_var_key_cols int,@Max_var_key_size int,@Fixed_key_size int,@Num_Rows float )
returns Bigint
AS
BEGIN
Declare @Index_Null_Bitmap int,@Var_Key_Size int,@Index_row_Size int,@Index_Row_per_Page float
Declare @level float,@Num_Index_pages int,@Index_Space_Used bigint
If (@Num_Null_key_cols>0)
Begin
set @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols+7)/8)
End
Else
Begin
set @Index_Null_Bitmap=0
End
IF (@Num_var_key_cols>0)
BEGIN
set @Var_Key_Size = 2 + (@Num_var_key_cols*2) + @Max_var_key_size
END
ELSE
begin
set @Var_Key_Size=0
END
set @Index_row_Size=@Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap + 1+6
set @Index_Row_per_Page = 8096/(@Index_row_Size +2)
set @level = 1 + floor(abs((log10(@Num_Rows/@Index_row_per_page)/log10(@Index_row_per_page))))
set @Num_Index_pages=0
Declare @i int
if (@level>0)
Begin
set @i=1
while(@i<=@Level)
Begin
set @Num_Index_pages = @Num_Index_pages + power(@Index_row_per_page,@level - @i)
set @i= @i + 1
End
END
set @Index_Space_Used = (8192 * @Num_Index_pages)
Return @Index_Space_Used
End
CREATE Function [dbo].[fn_GetLeafLevelIndexSpace]
(@Num_Leaf_Cols int,@Num_Var_leaf_Cols int,@Max_var_leaf_size int,@Fixed_Leaf_Size int,@Fill_Factor int,@Num_Rows float)
Returns bigint
AS
BEGIN
Declare @Leaf_Null_Bitmap int,@Variable_leaf_size int,@Leaf_Row_Size int,@Leaf_Rows_per_page int
Declare @Free_Rows_Per_Page int,@Num_Leaf_Pages float, @Leaf_Space_Used int
set @Leaf_Null_Bitmap= 2 + ((@Num_Leaf_Cols + 7)/8)
If (@Num_Var_leaf_Cols>0)
Begin
set @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2) + @Max_var_leaf_size
END
ELSE
Begin
set @Variable_leaf_size = 0
END
set @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size + @Leaf_Null_Bitmap + 1+ 6
set @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2)
set @Free_Rows_Per_Page= 8096 * (((100 - @Fill_Factor) / 100) / (@Leaf_Row_Size + 2))
set @Num_Leaf_Pages = ceiling((@Num_Rows/(@Leaf_Rows_per_page - @Free_Rows_Per_Page)))
set @Leaf_Space_Used = 8192 * @Num_Leaf_Pages
return @Leaf_Space_Used
END
CREATE function [dbo].[fn_GetNonClusteredIndexSize]
(@TableName varchar(100))
Returns bigint
AS
Begin
Declare @Num_Rows float,@Num_Key_cols int,@Fixed_key_size int,@Num_var_key_cols int,@Max_var_key_size int
Declare @is_clustered int,@index_id int,@is_unique bit,@Num_Diff_cols int,@Num_Null_key_cols int
Declare @Num_Index_pages int,@Index_Space_Used int,@Total_Index_space bigint
Declare @Num_Leaf_Cols int,@Num_Included_Cols int,@Leaf_Level_Space int,@Fill_Factor int
-- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL
-- No of Rows in a table
set @Total_Index_space=0
set @Leaf_Level_Space=0
-- insert info intom temp table
set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@TableName))
Declare @Tmp_Info Table
( Index_id int,Num_key_cols int,type int,is_unique bit,is_included smallint,fill_factor int,Num_Var_Key_cols int,
Fixed_Key_Size int,Max_Var_Key_Size int
)
Declare @tmp_Index_info Table
(sno int identity(1,1),index_id int,Num_key_cols int,type int,is_unique bit,Num_Var_Key_cols int,
Fixed_Key_Size int,Max_Var_Key_Size int,Num_Included_Col int,fill_factor int
)
insert into @Tmp_Info
select b.Index_id,count(c.name) Num_key_cols,b.type,b.is_unique,is_included_column,fill_factor,
IsNull((select count(c.name) from sys.columns e where e.object_id=c.object_id and a.column_id=e.column_id
and c.system_type_id in (165,167,231,34,35,99)),0) As Num_Var_Key_cols,
ISNULL((select sum(max_length) from sys.indexes d where d.index_id=b.index_id and d.object_id=c.object_id
and c.system_type_id not in (165,167,231,34,35,99)),0) As Fixed_Key_Size,
ISNULL((select sum(max_length) from sys.indexes d where d.index_id=b.index_id and d.object_id=c.object_id
and c.system_type_id in (165,167,231,34,35,99)),0) As Max_Var_Key_Size
--into @Tmp_Info
from sys.index_columns a,sys.columns c,sys.indexes b
where a.column_id=c.column_id and a.index_id=b.index_id and b.object_id=c.object_id and
a.object_id=b.object_id and b.object_id=object_id(@TableName) --and b.type>1
group by c.name,b.index_id,c.object_id,c.system_type_id,a.column_id,b.type,
b.is_unique,is_included_column,fill_factor
order by b.index_id
insert into @tmp_Index_info
select index_id As Index_id,sum(num_key_cols) as num_key_cols,type,is_unique,sum(Num_var_key_cols) as Num_var_key_cols,
sum(Fixed_key_size) as Fixed_key_size,sum(max_var_key_size) as max_var_key_size,sum(is_included),fill_factor
--into @tmp_Index_info
from @Tmp_Info where type>1 group by index_id,type,is_unique,fill_factor
IF Exists(select 1 from @Tmp_Info where type=1)
Begin
Set @is_clustered = 1
END
ELSE
BEGIN
Set @is_clustered = 0
END
Declare @row_Count int
set @row_Count=(select count(*) from @tmp_Index_info where type>1)
while (@row_Count>0)
begin
select @index_id=index_id,@Num_Key_cols=num_key_cols,@Fixed_key_size=fixed_key_size,@Num_var_key_cols=Num_var_key_cols,
@Max_var_key_size=Max_var_key_size,@is_unique=is_unique,
@Num_Included_Cols=Num_Included_Col,@Fill_Factor=fill_factor
from @tmp_Index_info where sno=@row_Count
If (@is_clustered=0)
Begin
set @Num_Key_cols = @Num_Key_cols + 1
set @Num_Leaf_Cols= @Num_Key_cols + @Num_Included_Cols + 1
END
ELSE
BEGIN
select @Num_Diff_cols=count(column_id) from sys.index_columns x,sys.indexes y
where column_id not in
(
select column_id from sys.index_columns a,sys.indexes b
where a.index_id=b.index_id and type>1 and a.object_id=b.object_id and
a.object_id=object_id(@TableName) and a.index_id=@index_id
)
and x.object_id=y.object_id and y.type=1 and x.object_id=object_id(@TableName)
and x.index_id=y.index_id
set @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols + @is_unique
set @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols + @Num_Diff_cols + @is_unique
END
select @Num_Null_key_cols=ISNULL(count(x.column_id),0) from sys.index_columns x,sys.columns y
where x.column_id=y.column_id and x.index_id=@index_id and y.is_nullable=1
and x.object_id=object_id(@TableName) and x.object_id=y.object_id
declare @index_name varchar(100)
select @index_name=name from sys.indexes where object_id=object_id(@TableName) and index_id=@index_id
select @Index_Space_Used=dbo.fn_getIndexSpace(@Num_Null_key_cols,
@Num_var_key_cols,
@Max_var_key_size,
@Fixed_key_size,
@Num_Rows)
select @Leaf_Level_Space=dbo.fn_GetLeafLevelIndexSpace(@Num_Leaf_Cols,@Num_var_key_cols,
@Max_var_key_size,@Fixed_key_size,@Fill_Factor,@Num_Rows)
set @Total_Index_space= @Total_Index_space + @Index_Space_Used + @Leaf_Level_Space
set @row_Count=@row_count-1
END
return Ltrim(str((@Total_Index_space))/(1024))
END
Create Function [dbo].[fn_GetTableSize]
(@TableName varchar(100))
returns varchar(25)
AS
Begin
Declare @TableSize varchar(25)
If Exists(select 1 from sys.indexes where object_id=object_id(@TableName) and type=1)
Begin
select @TableSize = dbo.fn_ClusteredIndexSize(@TableName) + dbo.fn_GetNonClusteredIndexSize(@TableName)
END
ELSE
BEGIN
select @TableSize = (dbo.fn_CalculateHeapSize(@TableName) + dbo.fn_GetNonClusteredIndexSize(@TableName))
END
set @TableSize = Ltrim(str(@TableSize)) + ' KB'
return @TableSize
END
