Non clustered index use in SQL server

Question

Can anyone tell me what is the use of having non clustered indexes in SQL server. As per my knowledge both the clustered and non clustered indexes make the searching easy..

Answer 1

The others seemed to have all touched on the same points, though I'll keep it short and provide a resource for you to get more information on this.

A clustered index is the table, and it (obviously) includes all columns. That may not always be what is needed and can be a hindrance when there are many rows of data in your result set. You can utilize a non-clustered index (effectively a copy of part of the table) to "cover" your query so that you can get a quicker response time.

Please check out this free video from world-class DBA, Brent Ozar: https://www.brentozar.com/training/think-like-sql-server-engine/

Good luck!

Answer 2

Clustered index is how the data for each row of the table is physically stored on disk (you can only have one of these index types per table), so all write operations' performance is based off of this index. And if you have to rebuild this index or move stuff around on this index, that can be very expensive.

Nonclustered indexes are just a listing of specific parts of the rows in a different order than how they are physically stored (you can have multiple of these index types per table), and a pointer to where it is actually stored. Nonclustered indexes are used to make it easy to find a specific row when you only know certain info about that row.

If you think about a typical text book as a database table, the clustered index is the set of actual pages of content for that book. Because logically it makes sense to write those pages in that order. And a nonclustered index is the index in the back of the book that list the important terms in alphabetical order. This just lists the word you are looking for, and the page number you can find it. This makes it extreamely easy for you to find what you need to read, when you are looking for a specific term.

Typically it is a good idea to make your clustered index an id that follows the NUSE principle (Narrow, Unique, Static, Ever increasing). Typically, you would accomplish this with a SMALLINT, INT, or BIGINT depending on the amount of data you want to store in the table. This gives you a narrow key because they are only 2, 4, or 8 bytes wide (respectively), you would also probably want to set the IDENTITY property for that column so that it auto increments. And if you never change this value for a row (making it static) -- and there is usually no reason to do so -- then it will be unique and ever increasing. This way, when you insert a new row, it just throws it at the next available spot on disk. Which can help with write speeds.

Nonclustered indexes are usually used when you use certain columns to search for the data. So if you have a table full of people, and you commonly look for people by last name, you would probably want a nonclustered index on the people table over the last name column. or you could have one over last name, first name. If you also commonly search for people based off of their age, then you may want to have another nonclustered index over the birthdate column for people. That way you can easily search for people born above or below a certain date.

Answer 3

The classic example explaining the difference is one of a phone book. The phone book, how it's physically structured from start to finish by Last Name (I think, it's been a while since I looked at a physical phone book) is analogous to the clustered index on a table. You can only have one clustered index on a table. In fact, the clustered index IS the table; it is how it's physically stored on disk. The structure of the clustered index contains the keys you define, plus ALL the data as well. Side note, in SQL, you don't HAVE to have a clustered index at all; such a table is called a "Heap", but that's rarely a good idea.

A nonclustered index by example would be if, say, you wanted to look up someone's entry in the phone book by address. You'd have an index at the back of the book with addresses sorted alphabetically, and then where in the phone book you can find that phone number. Doing this is called a "lookup". So a nonclustered index has:

• The keys you want to index (e.g. Address)
• A pointer back to the row in the clustered index (the last name of the person at that address)
• Optionally a list of included columns you might frequently need, but not want to have to go back to the clustered index to look up.

Whereas a clustered index contains ALL the data for each row, a nonclustered index is generally smaller because you only have your keys, your pointer and optionally included columns. You can also have as many of them as you want.

As far as how they return data, they're pretty similar, especially if you never have to do a lookup to the clustered index. A query which can get everything it needs from a nonclustered index is said to be "covered" (in that all the stuff you need is covered by the nonclustered index). Also, because clustered indexes are a linear ordering of the physical data, it makes range-based queries faster because it can find the start and end of the range simply by using an offset from the start of the clustered index.

Answer 4

One use is that you can only have one clustered index on a table. If you want more than one, the rest have to be non-clustered.