if object_id('dbo.sp_EstTableSizeCI', 'P') IS NOT NULL drop procedure dbo.sp_EstTableSizeCI
go
create procedure dbo.sp_EstTableSizeCI
@ObjId int, @Num_Rows int, @DataPages int,
@Indid smallint, @Has_UniqueClusteredIndex int,
@CIndex_Row_Size int OUTPUT, @CIPages int OUTPUT
as
/*
**
** Do not call the procedure directly. It will be called
** from the main procedure: sp_estTableSize
**
**
*/
set nocount on
declare @Num_CKey_Cols as smallint, @Num_Variable_CKey_Cols as smallint
declare @Fixed_CKey_Size as int, @Max_Var_CKey_Size as int
declare @CIndex_Null_Bitmap as smallint, @Variable_CKey_Size as int
declare @CIndex_Rows_Per_Page as int
declare @Free_CIndex_Rows_Per_Page as int, @T as int
declare @Est_Num_Pages as int
declare @Fixed_Size as int
declare @Variable_Col as tinyint, @Var_Size as int
declare @indkey as int, @TableName as sysname, @IndexColName as sysname
set @TableName = object_name(@ObjId)
-- Initialize additive variables
set @Num_CKey_Cols = 0
set @Fixed_CKey_Size = case when @Has_UniqueClusteredIndex = 1
then 0
else 4
end
set @Num_Variable_CKey_Cols = 0
set @Max_Var_CKey_Size = 0
-- Start with the first column in the clustered index
set @indkey = 1
while (@indkey <= 16) /* SQL Server 7.0 limits number of columns in an index to 16 */
begin
-- use Index_Col to get each column of the clustered index
set @IndexColName = INDEX_COL(@TableName, @Indid, @indkey)
if @IndexColName IS NULL Break
set @Num_CKey_Cols = @Num_CKey_Cols + 1
select @Fixed_Size = sc.length * (1 - st.variable),
@Variable_Col = st.variable,
@Var_Size = sc.length * st.variable
from sysobjects as so
join syscolumns as sc
on (so.id = sc.id)
join systypes as st
on (sc.xtype = st.xtype)
where sc.id = @ObjId
and sc.name = @IndexColName
set @Fixed_CKey_Size = @Fixed_CKey_Size + @Fixed_Size
set @Num_Variable_CKey_Cols = @Num_Variable_CKey_Cols + @Variable_Col
set @Max_Var_CKey_Size = @Max_Var_CKey_Size + @Var_Size
-- Get the next column in the clustered index
set @indkey = @indkey + 1
end /* while (@indkey <= 16) */
set @CIndex_Null_Bitmap = (2 + (( @Num_CKey_Cols + 7) / 8 ) )
set @Variable_CKey_Size = case when @Num_Variable_CKey_Cols = 0
then 0
else 2 + @Num_Variable_CKey_Cols + @Num_Variable_CKey_Cols +
@Max_Var_CKey_Size
end
set @CIndex_Row_Size = @Fixed_CKey_Size + @Variable_CKey_Size + @CIndex_Null_Bitmap + 9
set @CIndex_Rows_Per_Page = 8096 / (@CIndex_Row_Size + 2)
-- Get the Fill Factor used in the index (i.e., the Pad_Index factor)
declare @Fill_Factor as int
select @Fill_Factor = IndexProperty (@ObjId, object_name(@Indid), 'IndexFillFactor')
from sysindexes
where id = @ObjId
and indid = @Indid
set @Free_CIndex_Rows_Per_Page =
case
when @Has_UniqueClusteredIndex = 1
then case when @Fill_Factor = 100 then 0 else 2 end
else case when @Fill_Factor = 100 then 0 else 1 end
end
-- Calculate the number of pages required to store all the index rows at each
-- level of the index
declare @LevelPages as int, @CLevel as int
set @LevelPages = 0
set @CLevel = @DataPages -- number of pages needed to store table data
set @CIPages = 0
set @T = (@CIndex_Rows_Per_Page - @Free_CIndex_Rows_Per_Page)
-- According to Books Online (CREATE INDEX (T-SQL)):
-- "The number of rows on an intermediate index page is never less than two,
-- regardless of how low the value of FILLFACTOR."
set @T = case when @T < 2 then 2 else @T end
while (@LevelPages <> 1)
begin
set @LevelPages = ceiling (1.0 * @CLevel / @T)
set @CIPages = @CIPages + @LevelPages
set @CLevel = @LevelPages
end
