Tips for SQL-Indexes

Tips for SQL-Indexes

Clustered Index:
1. The queries that selected by a range of values or where you need sorted result.
2. The queries that use the “join” or “groupby” clause, SQL Server always uses internally merge sort.
3. The queries return large result set just not for few.
4. Table columns contain wide range of distinct values.
5. Don't create clustered index on a column that undergo frequent changes.
6. Always maintain proper “fillfactor” at the time of creating index. Normally the size of page is 6KB, “fillfactor” indicates to SQL keeps some free space in the page, if the data is not changing frequently then set “fillfactor” 60-70.
7. Avoid creating clustered index on composite key.
8. Keep the width of clustered index as narrow as possible.
9. Try to create unique clustered index, else internally SQL Server maintains 4 byte extra to maintain the unique.

Non-Clustered index:
1. Nonclustered in index are best for queries that return few rows it always goes to the clustered index to find the row.
2. If the same query is going to access over and over on the same table, consider creating a covering index on the table.
3. Contain columns frequently involves in search condition, such as where clause that returns exact result.
4. Queries that are use the “join” and “groupby”, and use filters.
5. Do not create more nonclustered index.