Maximum How many Row(s) will be there in Sys.Indexes view for Each table in SQL Server 2008/2008 R2 ?

 Posted by PandianS on 9/20/2010 | Category: Sql Server Interview questions | Views: 3829 | Points: 40
Answer:

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 | Alert Moderator 

Comments or Responses

Login to post response