DatabaseProcApplicationCreatedLinks
sybsystemprocssp_index_row_size_est  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** SP_INDEX_ROW_SIZE_EST
5     **
6     ** Internal stored sproc to return an estimate of the index row size given 
7     ** the table id and the index id. 
8     **
9     ** Parameters:
10    **	@table_id	- ID of the object for which index row size is being 
11    **			  etimated.
12    **	@index_id	- ID of the index for which index row size is being 
13    **			  estimated.
14    **	@table_type	- Lock scheme of the object passed. Will be either 
15    **			  0(allpages), 1(datapages), or 2(datarows).
16    **	@cols_to_max	- comma separated list of var column names for which 
17    **			  max size is to be used while index row size 
18    **			  estimation.
19    **
20    ** Returns:
21    **	@row_size	- the estimated index row size
22    **
23    **	return status 	= 1 => Invalid table ID
24    **			= 2 => Invalid index ID
25    **			= 3 => Invalid lock scheme
26    **
27    */
28    create or replace procedure sp_index_row_size_est(
29        @table_id int
30        , @index_id int
31        , @table_type tinyint
32        , @cols_to_max varchar(2060) = NULL
33        , @row_size int output
34    )
35    as
36        begin -- {
37    
38            /* Declare variables */
39            declare @sum_fixed int
40                , @sum_var int
41                , @num_var int
42                , @index_name varchar(255)
43                , @index_type varchar(20)
44                , @maxcols_in_key int
45                , @i int
46                , @key varchar(255)
47                , @table_name varchar(511)
48                , @type tinyint
49                , @length int
50                , @vartype smallint
51                , @dol_ind_fixed_ovhd smallint
52                , @dol_ind_var_ovhd smallint
53                , @apl_cind_fixed_ovhd smallint
54                , @apl_cind_var_ovhd smallint
55                , @apl_ncind_fixed_ovhd smallint
56                , @apl_ncind_var_ovhd smallint
57                , @ind_data_len int
58    
59    
60            /* 
61            ** RESOLVE (anushas) Why not return silently without raising errors here
62            ** as this is an internal only sproc?
63            */
64    
65            /* Do parameter check */
66            if exists (select * from sysobjects where id = @table_id)
67            begin
68                select @index_name = name
69                from sysindexes
70                where id = @table_id
71                    and indid = @index_id
72    
73                if @index_name is null
74                begin
75                    /* 18091, "The target index does not exist." */
76                    raiserror 18091
77                    return (2)
78                end
79            end
80            else
81            begin
82                /* 17461, "Object does not exist in this database." */
83                raiserror 17461
84                return (1)
85            end
86    
87            if @table_type not in (0, 1, 2)
88            begin
89                /* 17579, "Lock scheme Unknown or Corrupted" */
90                raiserror 17579
91                return (3)
92            end
93    
94            /* Initialize variables */
95            select @table_name = object_name(@table_id)
96                , @row_size = 0
97                , @sum_fixed = 0
98                , @sum_var = 0
99                , @num_var = 0
100               , @maxcols_in_key = 31
101   
102   
103           /* 
104           ** Look up each of the key fields for the index, and get the data length
105           ** and the number of variable length keys.
106           */
107           select @i = 1
108   
109           while @i <= @maxcols_in_key
110           begin -- {
111               select @key = index_col(@table_name, @index_id, @i)
112   
113               if @key is null
114                   break
115               else -- Process one key field
116               begin
117                   select @type = type
118                       , @length = length
119                       , @vartype = offset
120                   from syscolumns
121                   where id = @table_id
122                       and name = @key
123   
124                   if @vartype < 0
125                       select @num_var = @num_var + 1
126                   else
127                       select @sum_fixed = @sum_fixed + @length
128   
129                   -- If variable length column, check whether max length 
130                   -- or average length is to be used.
131                   --
132                   if (@type = 37 -- varbinary
133                           or @type = 39 -- varchar, nvarchar
134                           or @type = 155) -- univarchar
135                   begin
136                       select @sum_var = @sum_var
137                           + (case charindex(@key,
138                               @cols_to_max)
139                               when 0 then @length / 2
140                               else @length
141                           end)
142                   end
143               end
144   
145               select @i = @i + 1 -- Get next key field in this index
146           end -- } 	-- End of while
147   
148   
149   
150           /* 
151           ** Estimate the row size for this index as follows. 
152           ** 
153           ** Refer to the row formats for clarity (given in the comments at the
154           ** beginning of this file)
155           */
156           select @dol_ind_fixed_ovhd = 1 + 6
157               , @dol_ind_var_ovhd = 1 + 6 + (2 * @num_var)
158               , @apl_cind_fixed_ovhd = 1 + 4
159               , @apl_cind_var_ovhd = 1 + 4 + 2 + 1 + (1 * @num_var)
160               , @apl_ncind_fixed_ovhd = 1 + 6
161               , @apl_ncind_var_ovhd = 1 + 6 + 2 + 1 + (1 * @num_var)
162               , @ind_data_len = @sum_fixed + @sum_var
163   
164           if @table_type = 0 -- APL table 
165           begin
166               select @row_size = @ind_data_len
167                   + (case @num_var
168                       when 0 then (case @index_id
169                           when 1 then @apl_cind_fixed_ovhd
170                           else @apl_ncind_fixed_ovhd
171                       end)
172                       else (case @index_id
173                               when 1 then @apl_cind_var_ovhd
174                               else @apl_ncind_var_ovhd
175                           end)
176                   end)
177           end
178           else -- DOL table 
179           begin
180               select @row_size = @ind_data_len
181                   + (case @num_var
182                       when 0 then @dol_ind_fixed_ovhd
183                       else @dol_ind_var_ovhd
184                   end)
185           end
186       end --} 	-- Procedure done! 
187   


exec sp_procxmode 'sp_index_row_size_est', 'AnyMode'
go

Grant Execute on sp_index_row_size_est to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 71
 QTYP 4 Comparison type mismatch smallint = int 71
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 66
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 70
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 121
 MGTP 3 Grant to public sybsystemprocs..sp_index_row_size_est  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MUCO 3 Useless Code Useless Brackets in create proc 28
 MUCO 3 Useless Code Useless Begin-End Pair 36
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 181
 QAFM 3 Var Assignment from potentially many rows 117
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
121
 VUNU 3 Variable is not used @index_type 43
 MSUB 2 Subquery Marker 66
 MTR1 2 Metrics: Comments Ratio Comments: 35% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 13dec - 4exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 47 28

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysindexes  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_object_populate  
   called by proc sybsystemprocs..sp_spaceusage_object  
      called by proc sybsystemprocs..sp_spaceusage