x

The indexing concept

Hello all,

Please can anyone explain what is indexing concept in detail.Thanks In advance
more ▼

asked Nov 17 '11 at 12:06 AM in Default

Sql server R2 gravatar image

Sql server R2
3 3 4 4

Just curious, why the open ended questions? If you're just at the concept stage of learning SQL Server, I'd suggest reading the Books Online ( http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CCEQFjAA&url=http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms130214.aspx&ei=NAXFTs3WN4rg0QGK49DlDg&usg=AFQjCNEhybBatrE8nium44Q0-2MF8LMFqg&sig2=3MDWGJA5vo4947dLhfABGw) or maybe picking up a book (I have chapters in this intro book, http://www.amazon.com/Beginning-SQL-Server-2008-Administration/dp/1430224134/ref=sr_1_6?ie=UTF8&qid=1321534799&sr=8-6, and I'm writing another, http://manning.com/fritchey/, which you can read as it comes out)
Nov 17 '11 at 04:59 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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.
more ▼

answered Nov 17 '11 at 01:58 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

Nice. thanks for taking it a step further.
Nov 17 '11 at 02:07 AM Fatherjack ♦♦
Thants very good explanation.Thanks
Nov 17 '11 at 03:35 AM Sql server R2
Excellent explanation. Maybe you should rewrite the index chapter in my book.
Nov 17 '11 at 05:00 AM Grant Fritchey ♦♦
I'm blushing over here :)
Nov 17 '11 at 06:12 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 17 '11 at 12:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Thanks for answer
Nov 17 '11 at 03:34 AM Sql server R2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x122

asked: Nov 17 '11 at 12:06 AM

Seen: 1011 times

Last Updated: Apr 16 '12 at 06:13 AM