Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Durant » Stairway to SQL Server Indexes: Step 1,...
Add to briefcase
21 posts, Page 2 of 3
««123»»

Stairway to SQL Server Indexes: Step 1, Introduction to Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
X X-488698
Posted Thursday, June 23, 2011 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 08, 2012 5:25 PM
Points: 2, Visits: 11

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!
Post #1130717
Peter Maloof
Posted Tuesday, July 05, 2011 12:47 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:44 PM
Points: 295, Visits: 1,241
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 Maloof
Serving Data
Post #1136757
jpomfret7
Posted Wednesday, July 13, 2011 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 111, Visits: 555
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
Post #1140948
Peter Maloof
Posted Wednesday, July 13, 2011 7:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:44 PM
Points: 295, Visits: 1,241
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 Maloof
Serving Data
Post #1140967
whattheETL
Posted Tuesday, August 02, 2011 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:25 AM
Points: 25, Visits: 72
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.
Post #1152480
Olga B
Posted Thursday, September 01, 2011 5:24 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 22, 2013 5:58 PM
Points: 561, Visits: 440
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. ;)
Post #1168981
sandy-339368
Posted Tuesday, December 20, 2011 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 16, 2012 1:37 PM
Points: 2, 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
Post #1224702
joshdadulla
Posted Wednesday, March 28, 2012 7:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 7:46 PM
Points: 1, Visits: 10
This is my first time to understand Indexes. Thank you for the knowledge you have shared.
Post #1274809
PGarberick
Posted Friday, April 05, 2013 6:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:36 AM
Points: 441, Visits: 180
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!
Post #1439169
jeffreddy
Posted Friday, April 05, 2013 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 11:11 AM
Points: 2, Visits: 47
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 (), 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.
Post #1439220
« Prev Topic | Next Topic »

Add to briefcase
21 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.