SQL Indexes are used in relational databases to quickly retrieve data. They are similar to indexes at the end of the books whose purpose is to find a topic quickly. SQL provides Create Index, Alter Index, and Drop Index commands that are used to create a new index, update an existing index, and delete an index in SQL Server.

Type of Indexes

SQL Server supports two types of indexes:

1. Clustered Index
2. Non-Clusterd Index.

1. Clustered Index

A clustered index is the index that will arrange the rows physically in the memory in sorted order. An advantage of a clustered index is that searching for a range of values will be fast. You can create only one clustered index for a table.

2. Non-Clustered Index

A non-clustered index is an index that will not arrange the rows physically in the memory in sorted order. An advantage of a non-clustered index is searching for the values that are in a range will be fast. You can create a maximum of 999 non-clustered indexes on a table.

Create Index Example

create index DNoINdex on Emp(DeptNo)-- Simple Index
create index dnotedxi on emp(deptno asc,job desc)-- Composite Index

Update Index

Syntax :- Alter index <ind Name> on <object Name>  rebuild/Recognize/Disable.
Example :- alter index DNOiDX on EMp rebuild

The Rebuild option will recreate the computer index, the recognize option will reorganize leaf nodes of the b-tree to index and the disable option will disable the index. To enable index use rebuild option.

Delete Index

Syntax  :- drop index <indexname> on <object name>
Example  :- drop index doindex on student