Back to SQL Course

A SQL index is a quick lookup table for finding records users need to search frequently. An index is small, fast, and optimized for quick lookups. It is very useful for connecting the relational tables and searching large tables. Notice that not only creating a primary key creates a unique SQL index.

For example,

CREATE TABLE Bookstore
(ISBN_NO    VARCHAR(15) NOT NULL PRIMARY KEY, 
 SHORT_DESC VARCHAR(100), 
 AUTHOR     VARCHAR(40), 
 PUBLISHER  VARCHAR(40), 
 PRICE      FLOAT, 
 INDEX SHORT_DESC_IND(SHORT_DESC, PUBLISHER)
);

Let's see different types of indexes

• Unique Index

This index does not allow the field to have duplicate values if the column is unique indexed. If primary key is defined, a unique index can be applied automatically.

• Clustered Index

This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.

• Non-Clustered Index

It doesn't alter the physical order of the table and maintain a logical order of the data. Each table can have many non-clustered indexes.

What is the difference between clustered and non-clustered indexes?

• One table can have only one clustered index but multiple nonclustered indexes.

• Clustered indexes can be read rapidly rather than non-clustered indexes.

• Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in the table as it has separate structure from the data row.