In SQL index are used to
speed up the performance of queries. It reduce the number of database data
pages scanned. In SQL Server, a clustered index
determines the physical order of data in a table. Remember There can be only
one clustered index per table. An index is nothing but a copy of select columns from
a table that can be searched very efficiently that also includes a low-level
disk block address
Please Note that updating a table’s data with indexes takes more time
than updating a table without because the indexes also need an update. So you
should only create indexes on columns and tables that will be frequently
searched.
We have two type of index
in SQL
1. Clustered Index
The primary key
created for the Emp_id column will create a clustered index for the Emp_id column.
A table can have only one clustered index on it.
When creating
the clustered index, SQL server 2005 reads the Emp_id column
and forms a Binary tree on it. This binary tree information is then stored
separately in the disc. Expand the table Emp_Details and
then expand the Indexes.
You will see the following index created for you when the primary key is
created:
SELECT * FROM Emp_Details WHERE Emp_id=662
Execution of the query
will be
11. It will check the root node as 662 is less than 2000: move to
left node
2. On 1st level it will check as 662 will in between 501-1000:
move to right
It will get the records and send back the
results
2.
Non Clustered Index
A non-clustered index is
useful for columns that have some repeated values. Say for example, Emp_Id
column of a bank database may have 15 million rows. But, the distinct values of
account type may be 10-15. A clustered index is automatically created when we
create the primary key for the table. We need to take care of the creation of
the non-clustered index.
