SQL Programmers Blog - SQL Index, SQL Index Types, SQL Index Best Practices

What is SQL Index?

An index in a SQL database is similar to the index, or table of contents, in a book. The index in a book helps to quickly search for a particular topic instead of flipping through all the pages of the book. Likewise, when a database has large volume of data, then retrieving particular data from the database consumes more time and thus it becomes a performance issue. Creating indexes on the columns that are frequently accessed can be a good solution to retrieve the data quickly.

Indexes can be created on one or more fields of a table. Indexes are not applicable to the fields of large object (LOB) data types like text, image, and varchar(max).

SQL Index Types

In SQL Server, we can create different types of indexes.

1. Unique Index
   • This type of index does not allow the fields being indexed to have duplicate values.
   • A unique index will be created automatically when you define a primary key or unique constraint.
2. Clustered Index
   • This type of index reorders the physical data of the table based on the logical order of the key values.
   • Each table can have only one clustered index.
3. Non Clustered Index
   • Unlike Clustered Indexes, Non Clustered Indexes does not alter the physical order of database table rows.
   • It maintains the logical ordering information of the data alone.
   • SQL server 2008 allows each table to have up to 999 Non Clustered Indexes.

In SQL server, indexes can be created using either SQL Server Management Studio or T-SQL. Let’s see about dealing with indexes using T-SQL.

To create an index

CREATE INDEX index_name
ON table_name (column_name1, column_name2,..)

(or)

CREATE NONCLUSTERED INDEX  index_name
ON table_name (column_name1, column_name2,..)

By default, a non clustered index will be created unless the CLUSTERED key word is mentioned as follows.

CREATE CLUSTERED INDEX index_name
ON table_name (column_name1, column_name2,..)

To create a unique index

CREATE UNIQUE INDEX index_name
ON table_name (column_name1, column_name2,..)

The above query creates a unique non clustered index. To create a clustured unique index mention the CLUSTERED keyword explicitly as follows.

CREATE UNIQUE CLUSTERED INDEX index_name
ON table_name (column_name1, column_name2,..)

To drop an index when it is no longer needed

DROP INDEX table_name.index_name

Include Option

While creating a non clustered index, using the Include option the values of non key columns can also be stored along with the key column values being indexed. These included non key columns do not take part in the indexing process. Including the non key columns to the non clustered indexes can improve query performance when all columns selected in the query are in included in the index as key or non-key fields.  The performance improvement is because the query optimizer can locate all the columns within in the index, requiring less work to execute the query.

To include non key columns to a non clustered index,

CREATE INDEX index_name
ON table_name (column_name1, column_name2,..)
INCLUDE ( nonkey_column1, nonkey_column2,...)

SQL Index Best Practices

The following points must be considered before you decide to use indexes:

• Creating indexes on tables may improve the search performance. However, it slows down the performance of DML operations on the indexed tables.
• Indexing a table will not be efficient unless the table has large number of records.
• The performance of an index depends on the uniqueness of the columns involved. More duplicate values less index performance.
• Creating too many indexes on a table requires more physical space to store them.
• Creating an index on too many variables may lead to index that’s larger than the size of the table. An index must be relatively small compared to the table.