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)
Non-Clustered Index: -
A Non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a Non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
PRIMARY KEY constraints create clustered indexes automatically. A table can have multiple Non-Clustered Index.
Alternatively, a Non-clustered index could be created as: -
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)