Index is used for fast searching of data.
It provides a fast way to lookup data based on the values with in column in a tables so that it could help to speed up performance of queries.
Indexes in SQL works like Index of books.
It always helps to find data or fetch records in fast way.
If you want to find a particular topic in book, you will first refer to the Index of book,
In Index you can find easily topic by using page number because all pages with topics listed in index page.
Indexes helps to speed up SELECT queries but when we use UPDATE and INSERT statement,
Index takes more space on disk and slow down the performance of Queries.
Creation of Index:
Syntax of Index is mentioned below:
CREATE INDEX Index_Name ON Table_name (Column)
By using below syntax, we can confirm index which created on specific table.
EXEC SP_HELPINDEX Table_name
If you want to remove index from table, you can use the syntax:
DROP INDEX Index_Name on Table_Name
Types of Index:
Clustered Index :
• If you created Clustered Index on a table, it will help to sort the data in ascending order or descending order.
• By default, It create on table when define a primary key.
• It reorders the physical order of a table.
• There can be only one clustered Index on Table.
NON-Clustered Index :
• It cannot be sorted like clustered index.
• It maintains logical order of data.
• You can create more than one NON-Clustered Index on column in a table.
• You can create multiple NON_CLUSTERED Index on a single column.