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.