INDEXES IN SQL SERVER

         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:

·         CLUSTERED INDEX

·         NON-CLUSTERED 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 [UNIQUE] CLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN)

E.g.:

CREATE CLUSTERED INDEX CI ON EMP (EMPNO)

Note: if we want to maintain unique values in clustered/non clustered indexed column then specify UNIQUE keyword along with CLUSTERED INDEX/NONCLUSTERD INDEX

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 [UNIQUE] NONCLUSTERED INDEX INDEXNAME

 ON TABLENAME ( COLUMN1,…)

E.g.:

CREATE NONCLUSTERED INDEX NCI ON EMP (ENAME, SAL)

Ex:

 CREATE UNIQUE NONCLUSTERED  INDEX UI ON DEPT (DNAME)

COMPOSITE INDEX: If a Unique NonClustered index is created on more than one column then that concept  is called composite index.

CREATE UNIQUE NONCLUSTERED  INDEX  COI ON DEPT (DEPTNO, DNAME)

DEPTNO       DNAME

10              SALES

20              HR

30              IR

10              HR (Accepted)

20              SALES (Accepted)

30              IR (Repeated, Not accepted)

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

E.g.: SP_HELPINDEX   EMP

Syntax to drop the index:

DROP INDEX TABLENAME.INDEX.NAME

E.g.:

DROP INDEX DEPT.UI