if object_id('dbo.sp_EstTableSizeNCI', 'P') IS NOT NULL drop procedure dbo.sp_EstTableSizeNCI
go
create procedure dbo.sp_EstTableSizeNCI
@ObjId int, @Num_Rows int, @DataPages int, @CIndex_Row_Size int, @Has_UniqueClusteredIndex int,
@NCIPages int OUTPUT
as
/*
**
** Do not call the procedure directly. It will be called
** from the main procedure: sp_estTableSize
**
**
*/
set nocount on
declare @Indid as smallint, @IndexName as sysname
declare @Num_Key_Cols as smallint, @Num_Variable_Key_Cols as smallint
declare @Fixed_Key_Size as int, @Max_Var_Key_Size as int
declare @Index_Null_Bitmap as smallint, @Variable_Key_Size as int
declare @Index_Row_Size as int, @Index_Rows_Per_Page as int
declare @Free_Index_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 @LevelPages as int, @CLevel as int, @PrevCount as int
set @NCIPages = 0
set @PrevCount = 0
-- Get the index id's of the non-clustered indexes
declare NCI_cursor insensitive cursor
for
select indid, name
from sysindexes
where id = @ObjId
and indid < 255 -- exclude pointers to text / ntext / image pages
and IndexProperty(@ObjId, name, 'IsClustered') = 0
and IndexProperty(@ObjId, name, 'IsStatistics') = 0
and IndexProperty(@ObjId, name, 'IsHypothetical') = 0
for read only
declare @indkey as int, @TableName as sysname, @IndexColName as sysname
set @TableName = object_name(@ObjId)
open NCI_cursor
fetch next from NCI_cursor into @Indid, @IndexName
while @@FETCH_STATUS = 0
begin -- cursor loop
-- Initialize additive variables to zero
set @Num_Key_Cols = 0
set @Fixed_Key_Size = 0
set @Num_Variable_Key_Cols = 0
set @Max_Var_Key_Size = 0
-- Start with the first column in the non-clustered index
set @indkey = 1
while (@indkey <= 16)
begin
-- use Index_Col to get each column of the non-clustered index
set @IndexColName = INDEX_COL(@TableName, @Indid, @indkey)
if @IndexColName IS NULL Break
set @Num_Key_Cols = @Num_Key_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_Key_Size = @Fixed_Key_Size + @Fixed_Size
set @Num_Variable_Key_Cols = @Num_Variable_Key_Cols + @Variable_Col
-- If the table has a non-unique clustered index, then the 'uniqueifer' is internally treated
-- as a variable length column.
set @Num_Variable_Key_Cols = @Num_Variable_Key_Cols +
case when @CIndex_Row_Size > 0 and @Has_UniqueClusteredIndex = 1
then 1
else 0
end
set @Max_Var_Key_Size = @Max_Var_Key_Size + @Var_Size
-- Get the next column in the non-clustered index
set @indkey = @indkey + 1
end
set @Index_Null_Bitmap = case when @Fixed_Key_Size = 0
then 0
else (2 + (( @Num_Key_Cols + 7) / 8 ) )
end
set @Variable_Key_Size = case when @Num_Variable_Key_Cols = 0
then 0
else 2 + @Num_Variable_Key_Cols + @Num_Variable_Key_Cols +
@Max_Var_Key_Size
end
set @Index_Row_Size = @CIndex_Row_Size +
@Fixed_Key_Size + @Variable_Key_Size + @Index_Null_Bitmap + 9
set @Index_Rows_Per_Page = 8096 / (@Index_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, indid, 'IndexFillFactor')
from sysindexes
where id = @ObjId
-- According to Books Online (CREATE INDEX (T-SQL)):
-- for non-clustered indexes, space is always left on non-leaf pages
-- to accomomdate one additional row regardless of the setting of
-- FillFactor. I believe this is incorrect, and that, as in
-- SQL Server 6.x, a FillFactor from 0% to 99% results in one free row,
-- while a setting of 100% results in no free rows.
set @Free_Index_Rows_Per_Page = case when @Fill_Factor = 100 then 0 else 1 end
-- Calculate the number of pages required to store all the index rows at each
-- level of the index
set @LevelPages = 0
set @CLevel = @Num_Rows
set @T = (@Index_Rows_Per_Page - @Free_Index_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 @NCIPages = @NCIPages + @LevelPages
set @CLevel = @LevelPages
end
-- Populate the #ncindexes table created in sp_EstTableSize
insert into #ncindexes (IndexName, IndexPages) values (@IndexName, @NCIPages - @PrevCount)
set @PrevCount = @NCIPages
fetch next from NCI_cursor into @Indid, @IndexName
end -- cursor loop
close NCI_cursor
deallocate NCI_cursor
