Index in SQL Server with examples

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

 

·         WHERE clause (That is searching)

·         ORDER BY clause (That is sorting)

·         GROUP BY clause (This is grouping) etc.

 

Table scan:

Let us assume we have a student table with 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 unique column. If a table has many records, say 100K, then the number of scan 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, 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 scan and hence making the operations faster. In sql server there are two type of index:

·         Clustered index

·         Non-Clustered index

 

We'll discuss them on subsequent posts.

 

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

·         Heap: A table without any indexes.

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

 

Does index is physical object?

 

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