What is an index? What are the types of indexes?

Posted by

To view Verified answers click on the button below.

What is an index? What are the types of indexes? How many clustered indexes
can be created on a table? I create a separate index on each column of a table. what
are the advantages and disadvantages of this approach?
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the
data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you createe a
clustered index on a table, all the rows in the table are stored in the order of the clustered
index key. So, there can be only one clustered index per table. Non-clustered indexes have
their own storage separate from the table data storage. Non-clustered indexes are stored as
B-tree structures (so do clustered indexes), with the leaf level nodes having the index key
and it’s row locater. The row located could be the RID or the Clustered index key,
depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the
query optimizer can choose from all the existing indexes to come up with an efficient
execution plan. At the same time, data modification operations (such as INSERT, UPDATE,
DELETE) will become slow, as every time data changes in the table, all the indexes need to
be updated. Another disadvantage is that, indexes need disk space, the more indexes you
have, more disk space is used.