Index

What is an Index?

An index is used to gain fast access to specific information in a database table. For Ex: If a book contains some 1000 pages and if a reader wants to search specific topic/word, then he has to go through page by page till he finds his topic/word. But if an index-page is there in the book then the reader would find short time.

If a table contains 100000 records and if a user wants to search for a specific data, then DB has to go through row by row till finds the specific data. But if an index is used in the table, then DB would find the data quickly within a short time

Types of indexes:

• Clustered index
• Non-clustered index

Clustered Index:

Clustered indexes are those which arrange and stores the data in a particular order. For e.gg: A phone book sorts entries into alphabetical order. The difference is, once we find a name in a phone book, we have immediate access to the rest of the data for the name such as phone number and address.  A table can have only one clustered index as the table can be sorted in one order only. for e.g:  say if a table contains names, their weight and height then the clustered index can sort the table either by height or weight and not by both way, which is not possible.

Non-clustered Index:

A non-clustered index is a special; type of index in which the logical order of the index does not the physical stored order of the rows on disk. A table can have a max of 249 non-clustered indexes. For eg: the index page in a book. It actually gives only the page number where the specific topic is found but not the details of that topic.

Comparison between Clustered Index Vs Non-Clustered Index:

Clustered Index	Non-clustered Index
There can be only one clustered index in a table.	There can be 249 non-clustered indexes in a table.
The leaf node has the data page.	The leaf node has the index page.
It reorders the way records in table are physically stored.	The logical order of the index does not match with the physical order of the rows stored in the disk.

Limitations:

• More number of indexes can be used in a read-only table when compared to the table that needs frequent updation.
• Column that needs to be updated more often should not be indexed because every index is just one more thing that SQL server has to update when making a change to the actual data
• Clustered indexes don’t do we well when they based on multiple columns

Performance Tips:

• Try to create indexes on columns that have integer values rather than character values.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table. If you create a composite (muli-column) index, the order of the columns in the key is very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the left most of the key.
• Every index increase the time it takes to perform INSERTS,UPDATES and DELTED so the number of indexes should not be very much.
• When creating indexes, try to make them unique indexes if at all possible.
• Because it can often search through a unique index faster than a non-unique index because in a unique index, each row is unique and once the needed record is found , DB doesn’t have to look further.
• Indexes should be considered on all columns that are frequently accessed by WHERE, ORDER BY,GROUP BY clauses.