A non-clustered index doesn’t sort the physical data inside the table. Which means non-clustered index contains the index key value and row locator that point to storage location of the table data. A table can have more than one non clustered index. It is created to improve the performance to get the fast result.
The index will have pointer to the storage location
of the data. The pointer from an index row in a non-clustered index to a data
row is called a row locator. Non-clustered index is also a binary tree but it
doesn't create a physical order of rows. So the leave nodes of non-clustered index
contain PK (if it exists) or row index.
Inside the non-cluster index
Syntax to
create non cluster index.
CREATE NONCLUSTERED
INDEX <index_name>
ON <table_name>
(<column_name> ASC/DESC)
We can have
one or more columns in the index.
create nonclustered
index IX_Person_Address on person_Address (City)
See the index
We can
create 999 non clustered index on a table.
