Features 1) Improves performance 2) Could be built on one or more columns in a table or view also these are automatically created for primary keys and unique constraints We have two types of indexes 1) Clustered Index. 2) Non-Clustered Index Clustered Index Leaf Node contain actual data pages, data is sorted and stored based on index key. Clustered index are normally designed for a table which has a query which returns larger amount of data for example queries having where data between certain range Nonclustered Index. Leaf nod contains the pointer to the actual data pages. Non-Clustered index are normally designed for a table which have query that returns small matches for example for queries having where="value" Maintaining our indexes. Rebuilding and re-organizing Rebuild : Drops the index and recreated the index Reorganize : Just re-organizes the leaf nodes You should use re-org when fragmentation is less than 30-40 % In enterprise version we have option of online index rebuilding i.e index is accessible during the rebuilding process. |