Indexes are basically used to improve query performance.
But also have penalties in case of transactional tables (where insert/update/delete) frequently done.
There are two types of indexes in sql server.
- Clustered index
- Nonclustered index
Since the data is stored in B-Tree structure
Clustered index: sort and store the data rows in the table or view based on their key values.
- When we create primary key on table by default Clustered index is generated.
- There is only one clustered index in a table.
- When the table has clustered index called clustered table.
- Since the data stored in B-Tree the leaf node contains actual data in clustered index.
- Good for Sequential access and range selection.
NonClustered index: have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
- Data in pages in random order and logical data order in indexes.
- Have more than one nonclustered index in a table.
- Leaf node having the reference data not actual data.
- Create Index syntax will create nonclustered by default
Covering Indexes: When a nonclustered index includes all the data requested in a query (both the items in the SELECT list and the WHERE clause), it is called a covering index.With a covering index, there is no need to access the actual data pages,Only the leaf nodes of the nonclustered index are accessed.
Indexes on column are useful in following conditions
- column that are frequently used in search criteria
- columns used to JOIN different tables
- columns that are used as foreign key fields.
- columns Of having high selectability
- columns are used in ORDER BY clause
- Of type XML (primary and secondary indexes)
Index selectivity and Density:
selectivity:number of distinct key values in the table. Primary key, unique key are highly selective, higher selective index get better performance.
Density: number of duplicate key values in the table.
Fill-factor: percentage of space for data in leaf pages, if fill factor is 80% that means 20% space are free/empty on leaf page for future data growth.
Fill-factor guidelines: fill factor are depends how the data are accesses. The lower fill factor higher storage for the indexes.
- If the data inserted at the end of table fill factor should be 90-100%.
- If the data inserted anywhere table fill factor should be 60-80%.
While Creating an indexes please make sure the following points you need to considered.
- Make Clustered indexes on every tables.
- Keep indexes narrow one or fewer columns.
- Clustered index on a highly selective column.
- Make sure you considered performance risk for insert/update/ delete.
- Make clustered index on a column that is never update.
- Remove duplicate indexes.
- Check the default fill factor.
- Non-clustered indexes can be created in different file groups, which may increase performance.
See the below performance examples with and without indexes.
Table with no indexes (Select * from tablename)
/06/NonClusterIndex.png” alt=”NonClustered Index” width=”541″ height=”313″ /> NonClustered index Performance[/caption]Table with Nonclustered indexes on lastName
Table with clustered indexes on lastName
See the difference between performance of indexes. We can heavily reduced execution time by selecting only “Last Name”in Clustered / nonClustered indexes.
More to know about SQL indexes impact see the below post. Fragmentation, page splitting and index statistics