The indexing concept

Hello all,

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

more ▼

asked Nov 17, 2011 at 12:06 AM in Default

avatar image

Sql server R2
3 4 4 5

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, 2011 at 04:59 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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, 2011 at 01:58 AM

avatar image

Magnus Ahlkvist
20.9k 19 38 42

Nice. thanks for taking it a step further.

Nov 17, 2011 at 02:07 AM Fatherjack ♦♦

Thants very good explanation.Thanks

Nov 17, 2011 at 03:35 AM Sql server R2

Excellent explanation. Maybe you should rewrite the index chapter in my book.

Nov 17, 2011 at 05:00 AM Grant Fritchey ♦♦

I'm blushing over here :)

Nov 17, 2011 at 06:12 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Nov 17, 2011 at 12:15 AM

avatar image

Duncan Sutcliffe

(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, 2011 at 12:43 AM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

Thanks for answer

Nov 17, 2011 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 17, 2011 at 12:06 AM

Seen: 1360 times

Last Updated: Apr 16, 2012 at 06:13 AM

Copyright 2016 Redgate Software. Privacy Policy