• Tweet

• Tweet

In clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index.

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Writing to a table with a clustered index can be slower, if there is a need to rearrange the data or insert new data.

Clustered Indexes

1. Clustered indexes are indexes that uniquely identify the rows in an SQL table.
2. Every table can have exactly one clustered index.(similar to primary key)
3. You can create a clustered index that covers more than one column. For example: create Index index_name(col1, col2, col3.....).
4. By default, a column with a primary key already has a clustered index.

Non-clustered Indexes

1. Non-clustered indexes are like simple indexes. They are just used for fast retrieval of data,not  sure to have unique data.

In brief:

• In SQL Server, both clustered and non-clustered indexes are organized as B trees.Both types of indexes exist as B-tree structure.
• Kind of the indexes are clustered and non-clustered.
• Index is the way to order the records in a database according to the field values. It is the way to have fast access to the particular information.
• Indexes are created to the columns that are queried frequently.
• Indexes can be clustered and non-clustered indexes.
• Clustered index keeps all the records in the database in the order of clustered index key.
• There can be only one clustered index per table.
• Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table.
• Indexes improve query performance but it slows down data modification operations.
• Indexes consume disk space. For example, in an employee table, empno is a primary key, empname is a alternate key that may not be unique but still helps in identifying a row of the table. It accepts null values.