SQL SERVER INDEX

Clustered Index:--

In Clustered indexes, data pages and index pages are stored in one place. The data rows are stored in order based on the clustered index key.

The clustered index is implemented as a B-tree index structure the supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linkedin a doubly-linked list.

When delete operations are performed, index pages including data pages are marked as deleted but when new records are added, new index pages including data pages are stored/created .

Therefore, in case DML operations indexes slows performance of row retrieval.Because of storing key and data in one places in clusterd index, only one clustered indexcan be made in one table. In numeric values, clustered index should be made because of fast being sorted but should not be made in varchar data type it caused problem of being sorted. Clustered indexes should be made in master table.

Heaps :--

Heaps are tables that have no clustered index.

The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages.

The data pages are not linked in a linked list.

Non-Clustered Indexes:--

In non-clustered index, data pages and index pages are stored in separate places. Non-Clustered index do not effect the order of the data rows.

The Leaf level consists of index rows. Each index row contains the nonclustered key values, a row locator and any included, or non key, columns. The locator points to the data row that has the key value.

In non-clustered index, when delete operations are performed, index pages are marked as deleted and data pages remove spaces, and while inserting rows, that rows fill that space but index pages are newly created. That is why, while performing DML operations indexes slows performance.

Non-Clustered indexes are created on transaction tables.