What is an Index?
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.
To create non-clustered index:
create nonclustered index NIX_FTE_Name on Student (Name ASC);
What are key differences in clustered and non-clustered index?
|ATTRIBUTE||CLUSTERED INDEX||NON-CLUSTERED INDEX|
|Memory Requirements||Requires less memory for operations.||Requires more memory for operations.|
|Index Data||Index is the main data.||Index is the copy of data.|
|Number of indexes||Table can have only one clustered index.||Table can have multiple non-clustered index.|
|Storage||Clustered index has inherent ability of storing data on the disk.||Non-Clustered index does not have inherent ability of storing data on the disk.|
|Pointer||Clustered index store pointers to block not data.||Non-Clustered index store both value and a pointer to actual row that holds data.|
|Leaf Nodes||Leaf nodes are actual data itself.||Leaf nodes are not the actual data itself rather they only contains included columns.|
|Key||Clustered key defines order of data within table.||Index key defines order of data within index.|
|Table Records||Table records are physically reordered to match the index.||Logical order of index does not match physical stored order of the rows on disk.|