Indexes in SQL Server

                                                    INDEXES

                                                                                                                                               

                Indexes are used for faster access of data. If you apply too many indexes on tables then performance will decrease.

Types of Indexes

                                * Clustered Index

                                * Non-Clustered Index

                                * XML Index

                                * Spatial Index

Clustered Index:

A table can contain only one clustered index. If primary key is created on column in a table

then cluste index will be created automatically . Clustered Index will change the physical order of the records in a table.

CREATE TABLE emp (EmpID INT,EName VARCHAR(10),Sal INT )

CREATE CLUSTERED INDEX <INDEX-NAME> ON <Table-Name>(Column-Name)

CREATE CLUSTERED INDEX my_clustered ON Emp(EmpID)

Non-Clustered Index:

A table can contain 249 non-clustered indexes up to SQL 2005 and 999 non-clustered indexes from SQL 2008. If unique constraint is created on columns in a table then Non-Clustered index will be created automatically. It will not  change the physical order of the records in a table.

CREATE NONCLUSTERED INDEX NIX ON Emp(EmpID) include (Ename,Sal)

XML Index:

This is the new index introduced in sqlserver 2005,it is placed on a column whose datatype is XML.

Spatial Index:

This is the new index introduced in sqlserver 2008 R2 , it is placed on a column whose datatype is Spatial.

Disabling an index:

ALTER INDEX < INDEX - NAME > ON < TABLE - NAME > disable

Dropping an index:

DROP INDEX <TABLE-NAME>.<INDEX-NAME>

Rebuilding an index:

ALTER INDEX INDEX NAME ON <TABLE-NAME> rebuild

Index rebuild means it drops the existing index and recreates the index.

Reorganizing an index:

ALTER INDEX INDEX NAME ON < TABLE - NAME > reorganize

Index reorganize means physically reorganize the leaf-nodes of the page.

Advantages:

It increases the select query performance.

Drawbacks:

 Increase the size of the database.

 Data modification performance become slow.

Points to Remember

We can't apply indexes on columns whose data type is nvarchar(max) , text and ntext.