An index can be created before there is data in the table. Relational indexes can be created on tables or views.
Now in this Blog, I am going to discuss mainly about the Clustered Indexes.
- We will discuss where mainly we should use Clustered Indexes?
- What kind of queries will benefit from Clustered Indexes?
- How can I tell if a table needs an Index?
- How can I tell if a table that has Index that is not being used and hence wasting the valuable and scarce resources?
A Clustered index determines the physical order of data in a table. It is like a telephone directory that we normally use to find out the details of some persons or organizations, which arranges data by last name.
As the data in the table is stored in the table according to the Clustered Index so there can be only one clustered index per table. However, the index can be comprised of multiple columns (called composite index).
Clustered indexes are efficient for finding a particular row when the indexed value is unique. For instance, the fastest way to find a particular order information from Order table using the unique Order_ID column is to create a clustered index or Primary Key constraint on the Order_ID column.
Primary Key constraints create clustered indexes automatically if no clustered index is already existing on the table and a Nonclustered Index is not specified when creating the Primary Key constraint.
Clustered Indexes are also very efficient on columns that are often searched for some range of values. Because after the row with the first row is found using the clustered index, rows with subsequent index values are guranteed to be physically adjacent.
Clustered Indexes can also be created on the columns for a table which is frequently used to sort the data.
Before creating Clustered Indexes, it is important to understand how the data is accessed . Clustered indexes should be considered for the following situations:
- Columns that are mostly accessed sequentially.
- Queries that usually return large result sets.
- Queries that use the filters like BETWEEN , >=, <=, <, >
- Columns having large number of distinct values.
- Columns that are mostly used for GROUP BY, ORDER BY clauses.
- Columns that are often used in Joins.
Clustered Indexes are not good choices in the following situations:
- Columns that are frequently modified.
- Columns having wide values.
In my next blog I will cover two very interesting yet lesser known techniques that will help us to create the right indexes at right places which can dramatically speed up the database performance.