Jan 6, 2012

Index in sql server with examples

What is an index in SQL server?

An index is a way to organize data in a table to make some operations like searching, sorting, grouping etc fast. So, in another word, we need indexing when a SQL query has:

1. WHERE clause (That is searching)
2. ORDER BY clause (That is sorting)
3.  GROUP BY clause (This is grouping) etc.

Table Scan:
Let us assume we have a student table with the following schema:

CREATE TABLE Student(
    RollNo INT NOT NULL,
    Name VARCHAR(50) NULL,
    Country VARCHAR(50) NULL,
    Age INT NULL
)

And its data as follow:

RollNo
Name
Country
Age
101
Greg
UK
23
112
Sachin
India
21
109
Akaram
Pakistan
22
107
Miyabi
China
18
108
Marry
Russia
27
103
Scott
USA
31
110
Benazir
Banglades
17
111
Miyabi
Japan
24
102
Rahul
India
27
113
Nicolus
France
19

Right now student table has not any index. Consider on this query:

SELECT * FROM Student WHERE RollNo = 111

If we execute above SQL query, it will go for table scan i.e. it will scan the RollNo column from beginning to end until it doesn't get the record where RollNo = 111. In this way from RollNo 101 to 113, it has to scan 10 times not only 8 times since SQL server doesn't know RollNo is a unique column. If a table has many records, say 100K, then the number of scans will increase accordingly.

Time complexity of table scan is: O(n)

If we observe estimated execution plan it will look something like:


As we observed, a table scan is the least efficient way of scanning the record and hence SQL server has introduced index to optimize the scanning process i.e. to decrease the number of scans and hence making the operations faster. In SQL server there is two type of index:

1. Clustered index
2. Non-Clustered index


We'll discuss them in subsequent posts.

Note: In SQL server a table can be two types:

a. Heap: A table without any indexes.
b. Clustered index: If we create a primary (cluster) index on a table, the table itself change to clustered index from heap. It doesn't create any new object.  



Does index be a physical object?

Answer: Yes it is physical objects and is stored in the disk and occupies some disk spaces. 

7 comments:

  1. Replies
    1. Rakesh9/13/2013

      Its very useful to me

      Delete
  2. Anonymous9/30/2013

    great explaination

    ReplyDelete
  3. Keep it up!!! Awesome Explanation!!!

    ReplyDelete
  4. Anonymous11/07/2013

    Can someone plz explain why SQL server does 8 scans here?

    ReplyDelete
    Replies
    1. Anonymous8/25/2014

      because record with roll no equal to 111 is at located at 8th row

      Delete
  5. Very nice article. It starts from very basics and ends up to the point. Great work!

    ReplyDelete