Index - SQL

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.

Single Column index

A single-column index is created based on only one table column. The basic syntax is as follows.

E.G.

CREATE INDEX index_name

ON table_name (column_name);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

E.G.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

E.G.

CREATE INDEX index_name

on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Drop Index

DROP INDEX index_name;

Clustered Index.

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

E.G.

CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score

ON student(gender ASC, total_score DESC)

NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data.

A non-clustered index doesn’t sort the physical data inside the table.

In fact, a non-clustered index is stored at one place and table data is stored in another place.

This is similar to a textbook where the book content is located in one place and the index is located in another.

This allows for more than one non-clustered index per table.

Each table can have 999 nonclustered indexes.

E.G.

CREATE NONCLUSTERED INDEX IX_tblStudent_Name

ON student(name ASC)

Differences between clustered and non-clustered indexes.

There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.

Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.

Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.