Using without a clustered index in SQL Server
Others seemed to have all touched on the same points, although I'll keep it short and provide you with a resource to get more information on this.
A clustered index is a table and it (obviously) includes all columns. This may not always be what is needed and can be a hindrance when you have many rows of data in your result set. You can use a nonclustered index (actually a copy of a portion of the table) to "cover" your query to get faster response times.
Please check out this free video from the world class DBA, Brent Ozar: https://www.brentozar.com/training/think-like-sql-server-engine/
Good luck!
source to share
A clustered index is how the data for each row of a table is physically stored on disk (you can only have one of these types of indexes for each table), so the performance of all writes is based on that index. And if you need to rebuild that index or move stuff around that index, it can be very expensive.
Nonclustered indexes are just a listing of certain parts of a row in a different order than how they are physically stored (you can have multiple of these types of indexes per table) and a pointer to where it is actually stored. Nonclustered indexes are used to make it easier to find a specific row when you only know specific information about that row.
If you think of a typical text book as a database table, a clustered index is a collection of actual content pages for that book. Since it makes logical sense to write these pages in that order. And a non-clustered index is an index at the end of a book that lists important terms in alphabetical order. It simply indicates the word you are looking for and the page number you can find. This makes it easy for you to find what you need to read when you are looking for a specific term.
It is generally a good idea to make your clustered index id that follows the NUSE principle (narrow, unique, static, ever incrementing). Typically, you should accomplish this with 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 also probably want to set the IDENTITY property on that column so that it grows automatically. And if you never change that value for a string (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 drops it to the next available disk location. This can help with write speed.
Nonclustered indexes are commonly used when you are using specific columns to find data. So if you have a table full of people and you usually search for people by last name, you probably need a nonclustered index on the people table above the last name column. or you can have one last name, first name. If you also usually search for people based on their age, then you might need another non-clustered index on the date of birth column for people. Thus, you can easily find people born above or below a certain date.
source to share
The classic example to explain the difference is in the phone book. The phone book, as it is physically structured from start to finish by last name (I think it has been a while since I looked at the physical phone book), is analogous to a clustered index on a table. You can only have one clustered index per table. In fact, a clustered index is a table; it is physically stored on disk. The clustered index structure contains the keys you define as well as ALL data. Note that in SQL you don't have a clustered index at all; such a table is called a "heap", but this is rarely a good idea.
A non-clustered index, for example, would be if, say, you wanted to find someone in the phone book at. You will have an index at the end of the book with addresses sorted alphabetically and then where in the phone book you can find that phone number. Doing this is called "searching". So a non-clustered index has:
- The keys you want to index (for example, address)
- The pointer goes back to the row in the clustered index (the last name of the person at this address)
- Optional list of included columns, which you may often need but don't need to go back to the clustered index for searches.
While a clustered index contains ALL the data for each row, a nonclustered index is generally smaller because you only have your keys, pointer, and optionally included columns. You can also have as many of them as possible.
As far as they return data, they are quite similar, especially if you never need to search for a clustered index. A query that can get whatever it needs from the nonclustered index is considered "covered" (in that all the content you need is covered by the nonclustered index). In addition, because clustered indexes are a linear ordering of physical data, they speed up range-based queries because they can find the start and end of a range simply by using the offset from the start of the clustered index.
source to share