What is an Index? 

[plantuml]
@startmindmap
* Index
** Key built from one or more columns
** Speeds up fetching of rows from table associated with the key
@endmindmap
[/plantuml]

What is clustered index? 

A clustered index defines the order in which data is physically stored in a table.

Table data can be sorted in only way, therefore, there can be only one clustered index per table.

CREATE TABLE student
(
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,
    city VARCHAR(50) NOT NULL
)

Notice here in the “student” table we have set primary key constraint on the “id” column. This automatically creates a clustered index on the “id” column.

Constraint: There should be non key value, means the data should be unique.

What is non-clustered index? 

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.

It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.

When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.

[private]

[/private]

To create non-clustered index:

create nonclustered index NIX_FTE_Name
on Student (Name ASC); 

[private]

What are key differences in clustered and non-clustered index?

ATTRIBUTECLUSTERED INDEXNON-CLUSTERED INDEX
SpeedFaster.Slower.
Memory RequirementsRequires less memory for operations.Requires more memory for operations.
Index DataIndex is the main data.Index is the copy of data.
Number of indexesTable can have only one clustered index.Table can have multiple non-clustered index.
StorageClustered index has inherent ability of storing data on the disk.Non-Clustered index does not have inherent ability of storing data on the disk.
PointerClustered index store pointers to block not data.Non-Clustered index store both value and a pointer to actual row that holds data.
Leaf NodesLeaf nodes are actual data itself.Leaf nodes are not the actual data itself rather they only contains included columns.
KeyClustered key defines order of data within table.Index key defines order of data within index.
Table RecordsTable records are physically reordered to match the index.Logical order of index does not match physical stored order of the rows on disk.

[/private]


Rakesh Kalra

Hello, I am Rakesh Kalra. I have more than 15 years of experience working on IT projects, where I have worked on varied complexity of projects and at different levels of roles. I have tried starting my own startups, 3 of those though none of it were successful but gained so much knowledge about business, customers, and the digital world. I love to travel, spend time with my family, and read self-development books.