Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Stairway to SQL Server Indexes: Step 1, Introduction to Indexes Expand / Collapse
Author
Message
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: Wednesday, May 14, 2014 4:25 PM
Points: 2, Visits: 20

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
Posted Tuesday, July 5, 2011 12:47 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 323, Visits: 1,313
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
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: Today @ 10:18 AM
Points: 147, Visits: 732
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
Posted Wednesday, July 13, 2011 7:05 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:19 PM
Points: 323, Visits: 1,313
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
Posted Tuesday, August 2, 2011 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 1:03 PM
Points: 27, Visits: 113
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
Posted Thursday, September 1, 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, September 12, 2014 4:57 PM
Points: 582, Visits: 453
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
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
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
Posted Friday, April 5, 2013 6:04 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 457, Visits: 218
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
Posted Friday, April 5, 2013 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:32 AM
Points: 2, Visits: 58
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 ««123»»

Permissions Expand / Collapse