Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
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.




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 10: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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.