What is a table index ?
What are Non-clustered and clustered indexes ?
Lets try to understand it with a analogy, a library.
Consider a library, where books are arranged in sequence of Author names in the shelves. So, if you need a book written by 'Arundhati Roy' , you can go directly to the shelf which has the books written by she, instead of going sequentially through all the books.
This is like clustered index in database.
Now suppose, you realized that some books are rather requested by titles. But as we have already arranged books in sequence of their authors, we can not rearrange it with titles in sequence, so you created a list of title and it's shelf number, so you ask me a book title, and i can get it for you from the shelf noted with it.
This is some thing like, non-clustered index.
Now, consider that, we frequently need a report to show books availability. So, we created a list of titles and whenever somebody takes it we mark it as 'Not Available'. This means, if we need the report, we don't even have to go to the shelf to check the availability of the book, all required information is in the list, call it a covered index in database terms.
Continuing this analogy, table indexes can be viewed as follows,
1. In case of clustered indexes, table data is physically stored in order of keys.
2. There can be only one clustered index per table and multiple non-clustered indexes.
3. Clustered indexes are good for range searches.
4. Non-clustered indexes are good for random searches.
5. Inserts and deletes can become an expensive affair in case of clustered index, as data needs to be shuffled to stored physically sorted.
6. When, all the required columns in 'select' clause and 'where' clause of a SQL are available in a index key, the data page is not get accessed, resulting a good performance. This kind of indexing is called as 'covered index'.
