Indexes in sql server
This article gives the basic idea of sql indexes.It will give idea of 1) what is index
2) types of indexes
3) what is clustered index
4) what is non-clustered index
5)what is composite index
6) advantages of indexes
7)disadvantages of indexes
Index allow us to retrieve data fast without reading the whole table.Index is similar to index of a book.Indexes can be created on columns of tables.If you create an index on a column sql server search the row based on that column and will retrieve data fastly.
Ex:- create index idx_itemid on items(itemid)
Basically there are two types of indexes .they are:-
1)Clustered index
2)Non-clustered index
Clustered index:- A clustered index will store all the row along with the indexed column.
Indexed values are sorted in ascending or desending order.We can have only one clustered index on a table.Clustered index can be created when we create primary key for a table.
Ex:- create clustered index idx_itemid on items(itemid)
Non-clustered index:- A non-clustered index will store the columns on which index has created.So if u want to retireve data other than the columns in the index it has to get data from table again.We can have 255 non-clustered indexes on a table
Ex:- create nonclustered index idx_itemid on items(itemid)
In addition to the above types of indexes there are some other indexes
1)unique index:- when ever we create a unique constraint on a table sql server will generate unique index on the table.
eg:- create unique index idx_itemname on items(itemname)
2)composite index:- if we have more than one column in index then it is called composite index. We can have at max 16 columns in composite index.
3)XML index :- An index on large type xml objects
Advantages of indexes are 1) we can search data using index
eg:- select itemid,itemname,itemprice from items where item price between 14 and 16
2) we can sort data using index
eg:- select itemid,itemname,itemprice from items order by itemprice ASC
3) we can group records using indexes
eg:-select itemid,itemname,itemprice from items group by itemprice ASC
4) We can maintain unique columns in a table using index
eg:- create unique index idx_itemname on items(itemname)
Indexes drawbacks:-
1)indexes will be stored on hard disk hence the space will depends on size of the table and number of columns in the index
2)If any data modification is done sql server has to modify all the indexes on the table