Indexes in SQL Server :-

  • Index is a database object used to make data retrieval fast.
  • In database indexes are created on particular column.
  • The column on which the index is created, it’s called “index-key”.
  • index created on salary column looks as follows.

In sql server, searching for a particular record is of two types.

  •  Table scan
  •  Index scan

(I) Table Scan in SQL Server  :-

  • In table scan, each & every record as to be compared.

Then SQL server scan the entire file.

  • It consumes more times.

(II) Index Scan in SQL Server :-

  • In index scan, on Average SQL Server scans only half of the file.
  • Which is  much faster than table scan.

SQL Server uses Index in the following Cases  :–

(I) If where condition (or) order by clauses is based on the column on which the index is created.

Example  :-  i1(sal) Select * from emp  where sal > 2000

SQL server doesn’t following index in the following cases:-

(I) If where clause (or) order by clause is based on the column or which the index is not available.

Example :-  i1(sal) select * from emp where ename=’smith’.

(II) Where clause is based on not equal to [<>] operation, in this case also sql server doesn’t use index scan.

Example  :-  i1(sal) select * from emp where sal<>4000

Creating number of indexes or particular table degrades performance of DML operations.

To maintain the balance so created indexes on columns frequently accessed in where clause & order by clause.

  OLTP                              OLAP

Get “in”                             Get “out”

Insert                                 Select.

  • Indexes are less used OLTP, widely used OLAP.