Indexes

 What is Indexes in SQL.

 Indexes are used in relation database to retrieve the data to quickly.

Index is a pointer to data in a table.

Mainly an index increases the speed of data retrieval but it also increases the overhead of the data modification, like Insert, delete and update of the data. Now we consider some important points about indexes.


Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.


Why do we need an Index in a table?

Suppose we have a table of records of employees that contain a million records and we want to search for a specific id of an employee. A normal search system will search for the employee id in each row. This process is called “Table Scan”. A table without a clustered-index is called a “heap table”. This process can require a large amount of time if the records are at the end of the table.


For overcoming this problem we can also use indexes. An index arranges the data of the table in a sorted manner in the form of a B-Tree (Hash Tables and R-Trees are also used).


Types of Indexes.

There are the following two main index types:

1. Clustered Index 

2. Non Clustered Index



Clustered Index:

1. In clustered index records in a table are sorted in physical order.
2. Therefor table have only one clustered index it is uses in primary key.
3. The leaf nodes of a Clustered Index contains the data pages. It is like a dictionary, 
4. when we create a table clustered index are automatically created.

Example:


Create Clustered Index My_ClusteredIndex  
on Employee_Detail(Emp_IId)  


2. Non Clustered Index:

A Non-Clustered Index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a Non-Clustered Index does not consist of the data pages. Instead, the leaf nodes contain index rows.


A Non-Clustered Index also uses a B-Tree. Each node of the B-Tree contains a key value and a pointer. The pointer holds the reference of the table row. This means that with a Non-Clustered Index there is extra work required to follow that pointer to the row in the table to retrieve any other desired values.



Query:
Create unique Nonclustered Index NonClusteredIndex  
on Employee_Detail(Emp_Name,Emp_Age)  


Imp:
Clustered Indexes are only the logical order of the index. In other words, a Clustered Index contains a key and the value of the row but a Non-Clustered Index only contains a key and a pointer to the row.



Difference B/w Clustered and Non-Clustered Index

1. A Clustered Index is a physical ordering of the data but a Non-Clustered Index is a logical ordering of the data (more like a pointer to the data).
2. A table has only one Clustered Index but a table can have one or more Non-Clustered Indexes.
3. Clustered Indexes are used when the data is unique but a Non-Clustered Index can work with clustered and Non-Clustered Indexes.
4. Data in a Clustered Index is always sorted but for Non-Clustered Indexes that is not necessary. 

Create Index:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create Unique Index:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);