Index in SQL

Index in sql is created on existing tables to retrieve the rows quickly.

If we don't create any indexes then the SQL engine searches every row in table (also called as table scan). As the table data grows to thousand, millions of rows and further then searching without indexing becomes much slower and becomes expensive.

As an Example: - Take a Telephone directory in which all information is stored in alphabetically order. Particular record will point to proper data page.  

Indexes are of two types

1)    Clustered Index
2)    Non-Clustered Index

Clustered Index: -
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf
Node of a clustered index contains the data pages.

PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a non-clustered index is not specified when you create the PRIMARY KEY constraint.

A table can contain only one clustered index.
 
As an Example: - Take a Book with separate index in which particular record will point to index number and index number will point to particular data page.  

Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried in this way rather than by employee ID.
 

Query: 
CREATE CLUSTERED INDEX IX_ DummyTable1_EmpId on DummyTable1(EmpId)
 
Where IX_ DummyTable1_EmpId :- Clustered index name
                DummyTable1 :- Table Name
                EmpId       :- Column Name (which we want to make 
                               clustered)

CREATE NONCLUSTERED INDEX IX_ DummyTable1_ EmpId  ON DummyTable1(EmpId)

Where IX_ DummyTable1_EmpId :- Clustered index name
                DummyTable1 :- Table Name
                EmpId       :- Column Name (which we want to make 
                               Non-clustered)

Click here to go on website