What is SQL Server Index?

  

SQL Server Indexes are the objects used to optimize the performance of queries. It makes SQL queries select, update or delete run faster. The whole purpose of indexes is to make finding data rows in a table easier.

 

If a table is created with no indexes, then the data rows are not stored in any particular order. This structure is called a heap.

If a clustered index is created in a table, then data rows are stored in sorted order on the clustered index key making easier to find rows.

But a nonclustered index is rather complex. The data rows are not stored in order based on the noclustered key rather each nonclustered key value entry has pointers to the data rows containing the key value.

 

Here, I want to elaborate mainly the difference between using an index and not using it.

For that I first, created two tables one without index and other with index. Then, I inserted some sample data in both of them.

The SQL query used is shown below.

 

create table tbl_test_index ( pid int, cname varchar(50))

insert into tbl_test_index values (1, 'Nepal')

insert into tbl_test_index values (2, 'India')

 

create table tbl_test_index2 ( pid int , cname varchar(50))

create index test_index2 on tbl_test_index2(pid)

insert into tbl_test_index2 values (1, 'Nepal')

insert into tbl_test_index2 values (2, 'India')

 

Then, I tried to find out the cost of running a select query with order by clause.

For that I highlighted the following query and clicked "Display Estimated Execution Plan (Ctrl + L)" button on tool bar of SQL Query Analyzer to display execution plan.

 

select    *

from     tbl_test_index

order by 1

 

The output is shown below.

 

Fig: SQL Execution Plan 1

 

Then, I highlighted following query in query analyzer and viewed execution plan.

select    *

from     tbl_test_index2

order by 1

 

 

Fig: SQL Execution Plan2

 

From the first figure for case without index, following things can be obtained.

Total CPU cost = 0.0009

Total I/O cost = 0.0487

From the second figure for case with index, following things can be obtained.

Total CPU cost = 0.000812

Total I/O cost = 0.04375

Also, the estimated cost is lower in second case.

From these results, it can be concluded the use of indexes optimizes the performance of SQL Queries or makes queries faster.

However, the choice of column as key should be made carefully depending upon maximum use of the column.