Normally, When we create a new table, One entry will be there in Sys.Indexes view as 'HEAP' the Index_ID is '0', If we create a CLUSTERED Index on that table then, The 'HEAP' will be replaced as 'CLUSTERED' the Index_ID is '1'.
When we create a NONCLUSTERED Index on remaining columns then the Index_ID will be increased as 2,3,4,5....1005. Normally, a table can have maximum 999 NONCLUSTERED INDEXES and 1 CLUSTERED INDEX, Totally a table can have 1000 INDEXES.
But, The Index_ID in Sys.Indexes will be 0 or 1 to 250 and 256 to 1005 (Totally 1000 Indexes/Entries in Sys.Indexes View for a table). Then what about the 251 to 255 (5 Sequence have been reserved for Index Internals).
Finally, An Index_id will be 0 or 1 to 250 and 256 to 1005 (Maximum 1000 Entries will be there in Sys.Indexes View for each table), Minimum 1 entry will be there as 'HEAP' or 'CLUSTERED'
Asked In: Many Interviews |