Non Clustered Index

In non clustered index leaf node contains the row pointer instead of row itself. If a clustered index is defined on table than leaf node contains the clustered index value rather than row pointer. So physical order of table does not change.Non clustered index can be defined on a table or view with a clustered index or heap. A table can have maximum 249 non clustered index.

The row pointer is a combination of file identifier, page number and number of the rows in page, this row pointer is also called ROW ID.

When we have to use non clustered index?

1) Column which contains mostly the distinct values. If there are very few distinct values(less than 95%) suppose 1 or 0 than query will not use index since table scan will be more efficient.
2) Columns that are frequently used in search that return exact result.
3) Columns that are frequently used in join and grouping
4) Used for queries that return few rows.
5) Queries that return small range of data. Clustered index perform better for large range of data.
6) Queries that contain both clause where and order by.
7) Try to create index on integer column rather than character column, since it takes less space.

How non clustered index affect the table
1) Does not change the physical order of table.
2) Non clustered index is similar to the back index of the book.