SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Indexes: Step 1, Introduction to Indexes


Stairway to SQL Server Indexes: Step 1, Introduction to Indexes

Author
Message
X X-488698
X X-488698
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 21
I've been working with databases for about 15 years now, though more as a developer than a dba, and this is the first article I've read that explained indexing in a way that I understand. Well done!
Peter Maloof
Peter Maloof
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 1357
David:

I have to disagree that a phone book is an unclustered index.

Unless I'm mistaken, the white pages contain data physically sorted by
last name, first name and address. Once you access the entry
you're looking for, you have all the data; there's no bookmark to follow.

Maybe I'm misunderstanding something, but that sounds like a clustered index to me.

Thanks,
Peter


Peter MaloofServing Data
jpomfret7
jpomfret7
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 806
Peter Maloof (7/5/2011)
David:

I have to disagree that a phone book is an unclustered index.

Unless I'm mistaken, the white pages contain data physically sorted by
last name, first name and address. Once you access the entry
you're looking for, you have all the data; there's no bookmark to follow.

Maybe I'm misunderstanding something, but that sounds like a clustered index to me.

Thanks,
Peter



Peter,

I think the information we are looking for is the hat size rather than the phone number. Once we use the index to get the phone number we then have to use the phone number to get the information. He mentioned the physical houses are not in order which is why it wouldn't be clustered.

Jess
Peter Maloof
Peter Maloof
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 1357
Jess:

You're right. Phone books are used to demonstrate clustered indexes, so I missed the next step of getting hat sizes.

Thanks for pointing that out.

Peter


Peter MaloofServing Data
whattheETL
whattheETL
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 122
This is an excellent article that takes the abstract concept of a nonclustered index and explains it in a simple everyday example. Well done. I look forward to reading the next 14 articles this week.

Thank you.
Olga B
Olga B
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 455
Excellent writeup.

Like others who posted here, I have worked with indexes for a long time, and I can't say that I actually learned something new at Level 1, but this article really helped crystallize my understanding. You might say it indexed what I already knew. Wink
sandy-339368
sandy-339368
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Hi David,

Thanks for this descriptive tutorial. I clearly understood the Non-clustered and Clustered Indexes. Still few questions roaming around my brain which are as follows...

1. In Non clustered Index, Index key is sequential or non sequential?
2. Since Non clustered Index has its own space in memory, is it possible for us to view an index he moment we define it for a table?
3. The example of Finding hat Size using Index Key (Last Name, First Name) and BookMark (Phone Number) was clear to me. Still if I need to see this in a Table, i.e. relating index and bookmark to find "searched data". May you please help in providing an example on that.

Thanks much
-Sandeep
joshdadulla
joshdadulla
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
This is my first time to understand Indexes. Thank you for the knowledge you have shared.
PGarberick
PGarberick
SSChasing Mays
SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)SSChasing Mays (605 reputation)

Group: General Forum Members
Points: 605 Visits: 277
Thanks so much! Been having communication issues with other team member who keeps talking about indexing and I'm on the primary key jag. Now I understand what they're trying to say. Can't wait for the next article!
jeffreddy
jeffreddy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 60
Gary Noter (3/16/2011)
Well written; excellent analogies.

I've known of indexes for quite some time (nearly 20 years; oh I'm getting old[er]!) and have implemented and used them countless times.

I'm confident I implement indexes reasonable enough (:WhistlingSmile, though will definitely benefit becoming more technically knowledgeable of them.

Starting at Level 1 and moving forward, all the while with the mindset I'm 'learning' something new, will reintro and expand my skill set on the topic.

Thx.


I agree with Gary here. The analogies weren't that great. I've always taught the phone book example as a great clustered index example, where last name is the index. In a teaching setting, hand a co-worker a phone book and ask them to look up an entry for a person in the phone book, giving them the last name. As in your example, they find the name as you described, by doing a seek on the clustered index.

Ask another person to look up another person, only give this person the Phone Number. They would have to do a scan of every entry in order to find the person.

You can then discuss the need for another 'notebook' that contains all the phone numbers listed in order and the last name listed with it. Now, for the second scenario, you can use this 'notebook' to do a seek for the phone number. Once the number and lastname are found in the notebook, you can use the last name found there to do a seek in the phone book and get the full person record.

Looking forward to the rest of the series.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search