Hi chetan,
With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.
It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.
The primary difference between a non-clustered index and a clustered index is that all of the data is stored at the leaf level of the clustered index.
On a table that has a clustered index, the key, the column or columns that define the index, is also used by non-clustered indexes to point to the data (since the data is not stored with the non-clustered index).
If the table doesn't have a clustered index, non-clustered indexes still have pointers, but it's to the row identifier (RID) of the heap.
So what's the utility of a non-clustered index?
Let's say the cluster key is on something like social security number. In theory, there is only of these and it uniquely identifies a person, so it makes for a valid possible primary key and, frequently, the primary key is the primary path to the data, so it makes a good candidate for a clustered. So any time we want to see an individual's record, we pass the SSN and it immediately goes through the clustered index to retrieve that data.
So, what if we want to retrieve everyone with the first name of Maria? We can retrieve that data from the clustered key, but because first name is not part of the key, what happens is that all the data across the entire index is scanned to find all instances of Maria. So, we can add a non-clustered index to the table.
Now SQL Server can very quickly identify all instances of Maria without having to scan the entire table making the data access very fast. Depending on the columns being selected, now we have to go back to the clustered index, using the key value as the pointer to retrieve the additional columns. We can change this behavior using the INCLUDE operator to add some columns to the leaf level of non-clustered indexes, making them work like miniature clustered indexes, but you have to be careful about the use of this since it adds to the amount of data being stored and the amount of time it takes to add/edit/delete data.
A non clustered index is added to a table to provide additional search and sort mechanisms that may be needed on top of the search and sort mechanism provided by the clustered index.
For more details about Clustered and Nonclustered Indexes refer below link.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017
