Index in SQL can be thought of as index of a book. Suppose, We have a book of 50 pages and we don't have indexes. Now if we have to navigate to a random topic then we have to start with first page and then second and so on, No problem here we can do this easily. But what if we have a book of 1000 pages, Now searching a random topic will be difficult. Here comes the need of indexes, With indexes we will just go to the index and then see the corresponding page number and jumps directly over the topic without searching on every page.

Sql does the same when it comes to searching. It starts with the first row and will search row by row till the end. So, it is fine if we have limited row. But with increase in data, retrieval of data becomes slow.

Types of Indexes

1) Clustered 

a) It sorts and stores data rows in the table on the basis of their key values.

b) Data rows are stored in a sorted order only if table has a clustered index. 

c) There is only one clustered index is allowed per table.

d) Example: Primary key.

2) Non-clustered

a) A non-clustered index has non-clustered index key values and each key value points to a data row.

b) The pointer from index row to data row is called row locator.

Syntax of a Non-clustered index

CREATE NONCLUSTERED INDEX Index_Name ON Table_Name (Column_Name)

When to use index?

1) Query having WHERE clause. Example-: When data retrieval using WHERE clause becomes slow.

2) Query having ORDER BY clause. Example-: When data retrieval using ORDER BY clause becomes slow.

When not to use index?

1) Column that is updated frequently. Don't create index for column that is updated frequently as with each update index also needs to be updated.

Key Note

1) Indexes slow downs insertion, as with insertion indexes also needs to be updated.

2) Indexes slow downs deletion as well, as with deletion indexes also needs to be updated.