Indexing a SQL Table

  • Article

I found one of my old documents where I kind of summarized Indexes on a SQL Table, might help.

 Indexing SQL Table

Primary Key:

· Creates clustered index on the column

· Doesn't allow NULLS

 

 

Unique Key:

· Creates non-clustered index on the column

· Allows only one NULL

 

 

Index on a table in general:

· Good for:

Ø Columns that contains large number of distinct values. If there are very few distinct values such as 0 and 1, most queries will not use index because in this case table scan is more efficient

 

Clustered Index:

· Good for:

Ø Columns that returns a range of values using operators such as BETWEEN, >, >=, <, and <=

Ø Columns that are accessed sequentially

Ø Columns that are frequently accessed by queries involving join or GROUP BY clauses

Ø Environment where very fast single row lookup is required. Create a clustered index on Primary Key column on such environment

           

· Not Good for:

Ø Columns that undergo frequent changes - High volume transactional environment where data tends to be volatile

 

· Benefit:

Ø If you apply cluster index on the columns which is used by ORDER BY and GROUP BY clause, that will eliminate SQL Server to sort data because the rows are already sorted. That will enhance the performance.

 

Non-Clustered Index:

· Good For:

Ø Columns frequently involved in search conditions of query (WHERE clause) that returns exact matches.

Ø Applications where joins and grouping are frequently involved. Create multiple non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.