why we need non clustered index,when there is a clustered index to help?
Hi, when we have a clustered index that helps to speed up the data retrieving process by sorting out the data accordingly , why do we need non clustered indexes,i use to see the word POINTERS often while reading about non clustered index but what are pointers exactly?. we could retrieve the data of the column that does not have any indexes based on specifying the clustered index column in the SELECT statement, then why we need a non clustered indexes on other columns when we already have a clustered index on the table, sorry if this is an invalid question ,just its been in the head since the long time.
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.