When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index. Using indexes we can search as well as sort the record quickly.
Basically indexes in tables are concepts like page no in a book. It looks like B-Tree structures.
There is a single root page at the top of the tree, branching out into N number of pages at each intermediate level until it reaches the bottom, or leaf level, of the index.
The index tree is traversed by following pointers from the upper-level pages down through the lower-level pages.
In addition, each index level is a separate page chain. There may be many intermediate levels in an index.
There are 2 types of indexes in sql server. They are
1) Clustered index
2) Non Clustered index
1) Clustered index:
a) Clustered index is unique for any given table
b) Clustered index are stored in sequence order. Basically in a table there is only one clustered index.
c) Using this cluster index we can easily search the record.
d) The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index
Non Clustered Index:
a) Non Clustered index are stored in heap. Basically in a table we can have one or more than one clustered index.
b) The leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator