Some or all of this information may be specific to Microsoft SQL Server 2000, and is not guaranteed accurate for other RDBMS implementations.
A nonclustered index is a (relatively) simple way to improve the performance of read activities on a table. It should be noted that maintaining an index creates additional overhead for data modification (write) activities. Like a clustered index, a nonclustered index is a b-tree constructed from the data that it is used to help access. These types of indexes can be used together, in a technique that will be detailed in the clustered index node. In a nonclustered index, the leaves of the tree contain bookmarks to the data page that contains its parent row.
How it works:
A nonclustered index works much like the index in the back of a text book (except that books don’t grow on binary trees). You use the index to quickly search for the data you want and then follow the bookmark/page number to its location. In most cases this is obviously faster than reading the book cover to cover and hoping that it contains what you want.
An Example:
The simplest case is searching for a single row in an index on a column containing unique data. In this case I believe it is safe to say that for any nontrivial table, an index seek will always be faster than a table scan, as O(log n)<O(n) for sufficiently large n.
SELECT * FROM suppliers WHERE supplierID = 23795
Let us revisit the data retrieval analogy I began here. You have millions of suppliers’ profiles scattered all over your house in nothing even resembling a coherent order, and now you need to find out everything about a specific supplier. Of course you could run around the house like a madman, frantically digging through every box, for months on end until you’ve finally found the record you need, but there has got to be a better way. In the process of tearing out your hair and developing a brand new stomach ulcer, a spectral voice whispers arcane words in your mind. Desperate for any way to meet your deadline you speak the ritual aloud:
CREATE NONCLUSTERED INDEX suppliers_by_number ON suppliers(supplierID)
The house shimmers and shakes and opening your eyes you find on the table in front of you a large black book with gold letters etched across its cover “suppliers_by_number”. You open the massive tome and see a list of all of your suppliers, ordered by their ID number, with the locations of their summary sheet! Thinking back to past lectures on
binary searches, you open the book to the middle, and after a few moments of flipping pages, you find the item you want: “23795: under the freezer, third box from the left, ninth sheet from the top”. You run down to the basement, lift up the freezer, and find the information you needed, months sooner than you ever could have by yourself.
Covering a Query:
While the previous use of indexes can be very useful, an even more interesting situation can arise. Returning to the example of the manager with the unusual filing system, let’s introduce a new query and a new index to speed it up. Suppose that you wish to know the names of the first ten suppliers so that you can thank them for helping you get your start in whatever industry you happen to be in.
SELECT supplierID, companyName FROM suppliers WHERE supplierID < 11
Now, let us further assume that your muse/mentor/stress-induced-hallucination has provided you with the following index:
CREATE NONCLUSTERED INDEX suppliers_and_names ON suppliers(supplierID, companyName)
You open up your shiny new index and
eventually end up at the beginning where you see “1, Allied Widgets Inc.: on the roof, taped to the chimney”. You set down the book and prepare to get out your ladder when a
realization strikes you: why am I going to the roof, when all the data I need are right here,
in the index? You quickly jot down the first page of the index and go home early. (NOTE: I don’t believe SQL Server actually leaves the building after resolving a query quickly, but that may be implementation specific).
Review/Remarks:
A nonclustered index is a b-tree that is useful in locating your unordered (heap) data. A nonclustered index can also point to ordered data, since it only sorts references to the data.Nonclustered indices can cover a query if they are built over all of the columns referenced in the query.A covered query requires no I/O aside from reading the b-tree in to memory(no data pages are accessed).An index is not guaranteed to improve the results of all queries.The query optimizer may decide not to use the index if it is likely to not be useful.An index must be updated when the data in the table changes, causing write operations to take more time and resources.A single table may have many nonclustered indexes.Compare:clustered index