Difference between Clustered and Non Clustered Index in SQL Server 2005

Clustered Index

1. Leaf node contains the actual data pages.
2. The data row of the table are sorted and stored in the table based on their clustered index key (i.e. based on the index column(s)).
3. You can have only one clustered index per table.
4. The RowLocator in Clustered Index is the clustered Index key.

Non Clustered Index

1. Leaf node contains index pages instead of data pages.
2. You can have up to 249 Non Clustered Index per table.
3. The data row of the table are not sorted and stored in the table based on their clustered index key (i.e. based on the index column(s)).
4. The row locator in Non Clustered Index is a pointer to the row. Row locator is built based on the following.
   ROW ID (RowLocator)= file identifier + page number + row number on the page
5. You can have the functionality of Non key Columns (Included Columns) in case of Non Clustered Index.
6. Non key columns are stored only at the leaf level whereas Key columns (Non Clustered Index Key columns) are stored at all the levels of non clustered index.

What are Heaps?

1. Heaps are tables without clustered index.
2. Data Rows are not stored in a particular order.
3. Sequences of the Data pages are not ordered as well as they are not linked in a linked list.

Examples

Clustered Index Example

Clustered can be created in the following ways

1. Create Table with Primary key – this will by default create clustered index based on the primary key defined.
2. Create clustered Index using the CREATE CLUSTERED INDEX command.

–create table with primary key
CREATE TABLE Employee (empno NUMERIC (10) PRIMARY KEY, EmpName Varchar(10));

–check the existence of Index for the table

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘EMPLOYEE’);

–create table without primary key

CREATE TABLE Employee2 (empno NUMERIC (10), EmpName Varchar(10));

–Create Clustered Index using Clustered Index Command

CREATE CLUSTERED INDEX IDX_CLUST_EMP ON Employee2 (empno);

–check the existence of Index for the table

SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘Employee2′);

Non Clustered Index Example

– Adding a column the existing employee table

ALTER TABLE EMPLOYEE
ADD TAX_ID NUMERIC(10);

–create non clustered index on TAX_ID column
CREATE INDEX IDX_NON_CLUST ON EMPLOYEE (TAX_ID);

–check the existence of Index for the table
SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME=‘EMPLOYEE’);

This is a very basic and a quick information on the differences between Clustered and Non Clustered Indexes in SQL Server.