For searching a data in a faster way, we actually need some sort of cross reference facilities where for certain columns of information within a table, it should be possible to get whole records of information quickly. When the data became larger and larger, it is very much necessary to get to the data very quickly. This is where an index within RDBMS comes in. Using index you can access of information Rapidly and efficiently also Enforce for uniqueness constraints.

Def: An index is an on-disk structure associated with a table or views that speed retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a B-tree structure that enables the RDBMS (eg: SQL Server) to find the row or rows associated with the key values quickly and efficiently . It is a database object created and maintained by DBMS. It is essentially a list of the contents of a column or group of columns. Indexes are ordered so that extremely first search can be computed through them to find data.

 There are two type of indexes “Clustered Index” and “Non-Clustered Index”.

  • A clustered index is sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. Only one clustered index is possible for a table, since only one sort order can be defined for a table.
  • A non-clustered index is stored in a separate structure to the base table. So it is possible to create the non-clustered index on a different file group to the base table. It have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. You can add non-key columns to the leaf level of the Non-Clustered index to by-pass existing index key limits and execute fully covered, indexed, queries.

An index (Both clustered and non-clustered) can be defined either as unique or non-unique. A unique index ensures that the data contained within the unique index columns appear only once within the table, including “NULL”. A unique index is commonly implemented to support the constraints.  Indexes are automatically maintained for a table or view whenever the table data is modified. A non-unique index is also applicable as there can be duplicate data; a non-unique index has more overhead than a unique index when retrieving data.