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 Friday, April 5, 2013 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 11:12 AM
Points: 20, Visits: 73
Wouldn't the index in the back of a textbook be a better example of a non-clustered index? The index values are ordered and they contain bookmarks (page numbers) for lookups.
Post #1439242
Posted Tuesday, March 4, 2014 7:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 2,692, Visits: 3,383
jpomfret7 (7/13/2011)
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
I know this is old, but I am using this piece to help me demonstrate indexes to a group. I'm sorry, but this is still incorrect. The phone book (table) has ITs data organized by last name. That is how the data was being searched; i.e. the White Pages are an example of Clustered index. If we knew the address and not the last name, we could use the street index in the back of the white pages to go to the main part and find the phone number. THAT would be a non-clustered index. I am only posting this because many people come to the stairways to learn, and I want to make sure they understand correctly.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1547340
Posted Wednesday, March 5, 2014 7:44 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:14 AM
Points: 147, Visits: 730
SQLKnowItAll (3/4/2014)
jpomfret7 (7/13/2011)
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

I know this is old, but I am using this piece to help me demonstrate indexes to a group. I'm sorry, but this is still incorrect. The phone book (table) has ITs data organized by last name. That is how the data was being searched; i.e. the White Pages are an example of Clustered index. If we knew the address and not the last name, we could use the street index in the back of the white pages to go to the main part and find the phone number. THAT would be a non-clustered index. I am only posting this because many people come to the stairways to learn, and I want to make sure they understand correctly.


I just want to confirm what is wrong, my understanding (and it could be wrong) is that the phonebook is a clustered index. However, the information that we need to retrieve is the hat size for the girls which is not in the phonebook (clustered index) hence the 'lookup', or phone call to the girls houses to get the hat size.

I'm not sure this is a great example of a nonclustered index, in my head this feels like it would be a join to another table to get the information, but since the data we need is not on the page of the clustered index some kind of lookup is required.

Is this how you see it or are you proposing that in this situation to get last name, phone number and hat size we use a clustered index?
Post #1547795
Posted Wednesday, March 5, 2014 7:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 2,692, Visits: 3,383
jpomfret7 (3/5/2014)
SQLKnowItAll (3/4/2014)
jpomfret7 (7/13/2011)
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

I know this is old, but I am using this piece to help me demonstrate indexes to a group. I'm sorry, but this is still incorrect. The phone book (table) has ITs data organized by last name. That is how the data was being searched; i.e. the White Pages are an example of Clustered index. If we knew the address and not the last name, we could use the street index in the back of the white pages to go to the main part and find the phone number. THAT would be a non-clustered index. I am only posting this because many people come to the stairways to learn, and I want to make sure they understand correctly.


I just want to confirm what is wrong, my understanding (and it could be wrong) is that the phonebook is a clustered index. However, the information that we need to retrieve is the hat size for the girls which is not in the phonebook (clustered index) hence the 'lookup', or phone call to the girls houses to get the hat size.

I'm not sure this is a great example of a nonclustered index, in my head this feels like it would be a join to another table to get the information, but since the data we need is not on the page of the clustered index some kind of lookup is required.

Is this how you see it or are you proposing that in this situation to get last name, phone number and hat size we use a clustered index?
I don't see how any information here is used as a nonclustered index. Please don't get me wrong, the series is extremely helpful and later on the indexes are explained very well. This intro is very confusing though... I have had to correct several people who I have referred to this stairway. The biggest problem is
The white pages best represents the concept of a nonclustered index.
because the white pages IS exactly like a clustered index. It is a set of rows that is physically ordered by last name, first name, etc. In no way is "the white pages" an example of a nonclustered index. That's my problem.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1547803
Posted Saturday, July 19, 2014 7:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:13 AM
Points: 80, Visits: 341
Apparently, the Stairway to SQL Server Indexes uses the Adventureworks 2005 database.

I have tried (without success) to install the Adventureworks 2005 database on SQL Server 2012.

Perhaps an updated Stairway, based on the current sample databases, would be more helpful??

Thanks...

Post #1594352
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse