Please can anyone explain what is indexing concept in detail.Thanks In advance
asked Nov 17, 2011 at 12:06 AM in Default
Sql server R2
Adding on to @Fatherjack 's excellent explaination:
The phone book suits very well as a description of a CLUSTERED index. A clustered index is an index that is stored together with the rows in the table, so that the data itself is ordered.
But if you again think of the phonebook example, and you want to find phone numbers for everyone who lives on a specific street, you can't really use the clustered index to find that, because the pages and rows in the phonebook are ordered by surname,initial,address.
In the phonebooks delivered where I live, there's usually also an address appendix, from which one can find a person, and from that go and find the person in the phone book. In SQL Server, that's a typical NON CLUSTERED index. It's an index that is stored outside of the actual data. Using a non clustered index, you'll get a pointer to a row in your data, visit that row, and from that find the phone number.
A NON CLUSTERED index can also be a "covering" index. That means the index contains all the information you'll need. Assume that in a phonebook, you store information about surname, initial, address, phone number, occupation etc, and you often want to find out the street name for a specific phone number. Then you'd create a non clustered index containing phone number and street name. When SQL Server does SELECT Streetname FROM phonebook WHERE phonenumber='555-123-456' it will find the phonenumber in the nonclustered index, and immediately also find the street name. That way, SQL Server won't have to visit the actual row. In the phonebook analogy, you won't have to go to page 257, and find Andersson, M to find the street name, it's already in the appendix Phonenumber/streetname.
answered Nov 17, 2011 at 12:15 AM
Think of all the people in your area and how you use the phone book to find someones phone number. The phone book is an index that lets you locate the information you need. It has an order (surname then forename/initial) and has other information in it like the address and phone number you want.
You SELECT PhoneNumber FROM Directory WHERE Surname = AND Initial = AND Address =
If the index has all the information you need and is in the right order then the index is used to speed up the process to let you locate the information you need.
If the index is inappropriate because it is in the wrong order or doesnt have the information you need then the index isnt used - think of issue if phone book was in date of birth order, or had no initials information.
answered Nov 17, 2011 at 12:43 AM