Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2012 > BOOK: Beginning Microsoft SQL Server 2012 Programming
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Microsoft SQL Server 2012 Programming
This is the forum to discuss the Wrox book Beginning Microsoft SQL Server 2012 Programming Paul Atkinson, Robert Vieira ; ISBN: 978-1-1181-0228-2
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Microsoft SQL Server 2012 Programming section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 30th, 2017, 07:12 PM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Question about clustered index configuration

I'm reading chapter 9, Understanding Indexes, and for the most part it makes sense to me, but there's something that I don't understand.

On p. 322, it shows the flow of retrieval for a non-clustered index over a clustered table. I understand that the non-clustered index entries are searched first, and that each one that matches the query criteria is the key to the clustered index value for that record, and that the second part of the search consists of finding the record itself using the clustered value indexes that the non-clustered search returns.

But what I don't understand is the configuration of the clustered index in the illustration. As an example, the non-clustered index returns the following list:


--------------
| Tim | 102 |
--------------
| Tom | 157 |
--------------
| Tony | 209 |
--------------


Using the first entry in the list (Tim ==> 102) , the first clustered seek is labeled Clustered Seek: 102 and looks like this:


--------------
| Tim | 102 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Steve | 52 |
--------------


Using the second entry in the (non-clustered results) list, the second clustered seek is labeled Clustered Seek: 157 and looks like this:


--------------
| Tom | 157 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Steve | 52 |
--------------


My first question regarding the clustered index lists is:

Why are the index values in the lists not in sequential order? I thought that the way the clustered index works, based on the fact that they use B-Trees, was that each node stored its values in sequential order, which is how the index is able to determine where to place records in the subsequent nodes?

My second question is:

Why are Tim (102) and Tom (157) at the top of their respective lists?

Now, using the third entry in the non-clustered list, which is labeled Clustered Seek: 209, that clustered index node looks like this:


--------------
| Tim | 102 |
--------------
| Fred | 1 |
--------------
| Mike | 56 |
--------------
| Ralph | 74 |
--------------
| Tony | 209 |
--------------


My questions here is, why is Tony at the bottom? When this "table" is compared to the other two Clustered Seek tables, the non-clustered keys that are used to trigger those index searches appear at the top of the clustered index nodes.

I understood pretty much everything that I was reading until I saw that graphic, so if someone could explain this to me, that would be greatly appreciated.

Thanks.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
i am getting error when i create this non clustered index Tijuana BOOK: Beginning Microsoft SQL Server 2008 Programming ISBN: 978-0-470-25701-2 0 August 6th, 2012 04:02 PM
Clustered Index Scan bmains SQL Server 2000 5 June 7th, 2010 11:35 AM
Clustered and Non-clustered Index plugsharma SQL Language 0 December 17th, 2008 09:21 AM
Page splits - Clustered vs Non-Clustered Index carumuga SQL Server 2005 3 October 20th, 2008 04:23 AM
Size of Clustered Index MikeSchnell SQL Server 2000 0 May 7th, 2004 02:34 PM



All times are GMT -4. The time now is 06:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.