In this blog, I’m explaining about Indexes in SQL

Indexes  are created on columns in tables or views. The index provides a fast way to look up data base  on the  values  within those  columns.

Types of indexes

1. Clustered Indexes
2. Non Clustered Indexes

Cluster Indexes

Only 1 clustered index allowed per table physically rearranges  the data in the table to confirm to the  index constraints for use  on columns that  are frequently searched  for  ranges of data for use on  columns with  low  selectivity.

Example:

Create TABLE [dbo].[EmployeeDetail] ADD  CONSTRAINT [PK_EmployeeDetail] PRIMARY KEY CLUSTERED 
(
            [EmployeeID] ASC
)

Non Clustered Indexes

A non clustered Index is useful for columns that have some repeated values.  A table  can have  more than one Non-clustered index. Non clustered column always depends on the clustered column on the database.

Example:

CREATE UNIQUE NONCLUSTERED INDEX [IX_EmployeeDetail] ON [dbo].[EmployeeDetail] (
 [FirstName] ASC,
 [LastName] ASC
)

 Example:

 select * from EmployeeDetail with(index(IX_EmployeeDetail_1))