question

This is a perfectly valid question. 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 (how data is stored when there is no clustered index). 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 index (where the data is stored). 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 Leonard? 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 Leonard. So, we can add a non-clustered index to the table. Now, SQL Server can very quickly identify all instances of Leonard 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. In a nutshell, 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.