When a table is created and does not have an index, this is called a heap. When new values are added to this table, values are added in no particular order. Using this structure, if we were to search for a particular value, we’ll have to search through every single row in the table not only because there is no order to the data, but even if we found a match, we wouldn’t know if there were no more searches later on. This is called a table scan and is very inefficient because we have to touch every record in the table.

Using an index will help you to find information faster by providing values. A clustered index sorts the values in the table and there can only be one clustered index per table. In SQL Server, the primary key constraint automtically creates a clustered index on that particular column. The clustered index stores the records in the student table in the ascending order of the primary key. This allows it to prevent scanning the entire table and improve efficiency when looking up the record.

You can use the following script to create a new clustered index. Be sure to delete the previous/default clustered index when you first created the table first, though!

use PhoneDB

CREATE CLUSTERED INDEX IX_TableName_ColName1_ColName2
ON TableName(ColName1 ASC, ColName2 DESC)

This creates a clustered index named IX_TableName_ColName1_ColName2 on the TableName table. The index is on the ColName1 and ColName2 columns, which is called a “composite index”. This will sortall records in the ascending order of ColName1. If ColName1 s the same for two or more records, those records are sorted in the descending order in the ColName2 column.

A nonclustered index is a data structure that improves the speed of data retrieval from database tables. A nonclustered index stores and sorts data separately from the data rows in the table. It is essentially a copy of selected columns of data from a table with the links to the associated table.

A nonclustered index can help you find data quicker than searching the underlying table because it uses a B-tree data structure in its underlying implementation, which is much more efficient in terms of complexity. Queries that ping a nonclustered index can be answered entirely by the data in the nonclustered index, or the nonclustered index can point to the rows in the underlying table.

To create a nonclustered index you can use the following syntax:

use PhoneDB

CREATE NONCLUSTERED INDEX IX_TableName_ColName
ON TableName(ColName ASC)

The above script creates a nonclustered index on the ColName column of the TableName table. The table data and the index will be stored in different places. The table records will be sorted by a clustered index if there is one. The index will be sorted according to its definition.