September 1, 2011 at 5:25 pm
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. 😉
December 20, 2011 at 12:21 pm
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
March 28, 2012 at 7:43 pm
This is my first time to understand Indexes. Thank you for the knowledge you have shared.
April 5, 2013 at 6:04 am
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!
April 5, 2013 at 7:43 am
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 (:Whistling:), 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.
April 5, 2013 at 8:14 am
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.
March 4, 2014 at 7:47 am
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.
Jared
CE - Microsoft
March 5, 2014 at 7:44 am
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?
March 5, 2014 at 7:54 am
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.
Jared
CE - Microsoft
July 19, 2014 at 7:15 pm
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...
May 21, 2015 at 7:23 am
A very good point. And something all authors should consider when posting code/scripts. It is important to know where to get the sample database (and which version) on which the discussion is based. The adventureworks database has changed (and will continue to do so) over time to demonstrate the new features of sql server. The scripts in this article will fail with the current (2012) version.
June 4, 2015 at 3:17 am
Scot Morris (5/21/2015)
A very good point. And something all authors should consider when posting code/scripts. It is important to know where to get the sample database (and which version) on which the discussion is based. The adventureworks database has changed (and will continue to do so) over time to demonstrate the new features of sql server. The scripts in this article will fail with the current (2012) version.
uhmm, im sorry.. But im using SQL Server 2012, and everything seems fine.
Could you please show me which part of the script that should be getting fail in SQL 2012?
Im affraid i've using the wrong version of AdventureWorks DB :unsure:
FYI :
I used AdventureWorksDB 2005 which i downloaded from http://msftdbprodsamples.codeplex.com/releases/view/4004
..and attach it into my SQL Server 2012 without any problems
September 18, 2015 at 3:58 pm
Nice series by David.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply