Why index is used ?

Index is used to access the data quickly from the table in database.

Let us assume if no index is created on the table. Based on requirement, it is required to get the data from the table which is at the last. When we trigger a query and if no index is available, system tries to read the table sequentially from beginning. So it is a time consuming process and slowing down the process.

When we trigger a query and if the table has index created on it, system uses search algorithms like binary search or some other search algorithms to get the data from the table. The retrieval using index saves time and increases the performance as well.

Index can compensate this by deducing minimal number of scans thus we fetch data in less time. Index is not visible.

What is index ?

Indexes are used by database search engine to speed up data retrieval from database. Index acts like a pointer to data in database.

• Indexes are created on columns in the table. Once index created on the table, a separate table was created called index table.
• Index always in unique that no two rows cannot have the same value.
• Index holds a key value that points data in database.
• Index table contains the mapping between keys and the corresponding data.
• Whenever we try to fetch data using index, search engine directly gets the data by using mapping from index table.
• Index have works fast on SELECT with WHERE Clause. It slows down on UPDATE because the Index table also needs to update with the corresponding keys.
• While defining PRIMARY and UNIQUE Constraints, Index gets automatically created for the table.

There are two types of Indexes. Those are -

• Clustered Index
• Non-Clustered Index.

Clustered index

Clustered Index was like a B-structure, where the root node at the top and leaf node at bottom. It searches the data from top to bottom. Clustered Index arranges the data physically in sorted order in the memory. Searching for range values by using clustered indexes became quite easy. We can create one clustered Index for one table. If we try to create more Cluster indexes on one table, SQL shows an error. In leaf nodes, Clustered index have actual values of the table.

Non-clustered index

Non-Clustered Index cannot arrange the data physically in sorted order in memory. Non-clustered Index have key values and their corresponding row locator. Key value points the data using row locator in clustered table. We can create more than one Non-Clustered indexes for one table. In leaf nodes, Non-clustered index have key values and point locators.

• Increases performance.
• Fetch data quickly.
• Identify the rows uniquely in the table.
• Used for sorting purpose.
• GROUP BY clause used to group records or aggregate values.

• Index need extra space on the disk to store index table.
• Performance gets decrease while using UPDATE, DELETE and INSERT Statement because the index also needs to update.
• The table that contain too many indexes also reduce the performance.

When should indexes are not used?

Even indexes are used to increase performance, there are some cases where we cannot use indexes are listed below -

• When table was small.
• The column that are continuously modifying should not be part of index.
• It's not suggestable to use indexes on tables where INSERT, UPDATE and DELETE Commands using frequently.
• Column that are contain high number of NULL values are not to be indexed.