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);