Types of Indexes in SQL Server – SQL Server Tutorial

Types of Indexes in SQL Server  :-

1. Simple index
2. Composite index
3. Unique index
4. Non clustered index
5. Clustered index 

(I) Simple index in SQL  :-

• If index is  created on one column,then it is called ”simple index”.

Syntax  : Create index <name> on<tabname>(colname).

Example :-  Create index i1 or emp(sal)

Note  :-    Query execution steps  :- 

• Query parsed → parsing means → compare the query
• Query optimized  → optimization means → preparing difference.
• Query executed  →  plans executing the query,estimating the cost each plan .

Indexes are created by user and used by system.
We can’t see the values of index.

(II) Composite index  in SQL  :-

• Index is created on more than one column,then it is called “composite index”.

Example :- Create index i2 on emp [deptno,job] (where we use this above index means as follows)

Select * from emp order by dept no,job

Select * from emp where deptno=10 and job=’clerk’

Note  : SQL server doesn’t perform composite index on ’OR’ operator.

(III) UNIQUE INDEX in SQL:-

Unique index doesn’t allow duplicate values in the column on which index is created.

Create unique index  i3 on emp (ename)

Different methods to enforce uniqueness

• Declare pk/unique constraint
• Create unique index.
• Create  db trigger.
• Declare column with identity.

When primary key declare then sql server unique index Primary key columns are automatically index by  SQL Server.by default SQL Server creates unique index on primary key column.