INDEXES

Indexes in sql server is similar to index in text book…Indexes are used to improve the performance of queries.

Indexes are generally created for following columns:

Primary key column
Foreign key column: frequently used in join conditions.
Column which are frequently used in where clause
Columns, which are used to retrieve the data in sorting order.

Indexed cannot be created for following columns:

The columns which are not used frequently used in where clause
Columns containing the duplicate and null values
Columns containing images, binary information, and text information.

Types of Indexes:

1. Clustered index
2. Non-clustered index
3. Unique index
Composite index

Clustered index:

only one clustered index is allowed per table. The order of values in a table order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.

Syntax: create clustered index indexname on tablename (column)

Example: create clustered index c1 on emp (empno)

NonClustered index:

It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.

Max no. Of non-clustered indexed allowed for table is 249

Syntax: create nonclustered index indexname on tablename (columns)

Example: create nonclustered index NCI on emp (ename, sal)

Unique Index:

An index with unique constraint. It will not allow duplicate values.

Syntax: create unique index indexname on tablename (column)

Example: create unique index UI on dept (dname)

Composite Index:

If a unique index is created on more than one column is called composite index.

Create unique index coi on dept (deptno, dname)

Sp_HelpIndex:

This stored procedure is used to display the list of indexes, which have been placed on different columns of a specific table.

Example: sp_helpindex emp

Syntax:

Drop index tablename . indexname

Example: drop index dept. ui

INDEXES