SQL | NON Clustered Index
The non-clustered index resembles a book's index. If you want to read a certain topic or chapter, you can jump right to that page by utilising the book's index, which includes the chapter name and page number. No need to go through each and every page of a book.
The data is stored in one place, and the index is stored in another place. Since the data and non-clustered index is stored separately, then you can have multiple non-clustered indexes in a table.
In a non-clustered index, the index contains the pointer to data.
Why create non-clustered indexes
1. The main benefit to having a non-clustered index on a table is it provides fast access to data.
2. The index makes it possible for the database engine to quickly find data without having to browse the full table. It is crucial to add the appropriate indexes as a table grows larger since, without them, query speed would drastically suffer.
When should non-clustered indexes be created
1. When there is more than one set of columns that are used in the WHERE clause of queries that access the table. A second index, besides the primary key will speed up execution times and reduce IO for the other queries.
2. if your queries frequently require data to be returned in a certain order (order by), having an index on these columns can reduce the amount of CPU and memory required as additional sorting will not need to be done since the data in the index is already ordered.
Create a non-clustered index
Creating a non-clustered index is basically the same as creating clustered index, but instead of specifying the CLUSTERED clause we specify NONCLUSTERED.
We can also omit this clause altogether as a non-clustered is the default when creating an index.
