January 28, 2011 at 9:44 am
A phone book analogy is often used here. You have a list of people's names in a phone book. The root would be analogous to the first letter of their last names. The intermediate would be subgroups of that, so, for example, Smith to Styles (but not their individual values), and the leaf would be the individual entries (each person's name (and telephone number)).
(Changed my avatar from a Husky to Carl from the movie UP! for the new year. :-))
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
January 28, 2011 at 9:45 am
Yes! Now it makes sense. I couldn't grasp the Intermediate Level and it's use.
Danke!
January 28, 2011 at 9:50 am
vj2173 (1/28/2011)
From your diagram, am I to assume that the root page contain the indexed column and all of the values from the index column. From1 to 137?
No. Just 1 and 137. It doesn't need any values between those, the root only stores the starting value for each page beneath it
Secondly, the Intermediate pages (level) hold segments of those values from the indexed column?
Again, no. The intermediate pages in that diagram contain only the values specified. Like the root, the lowest key value for each page beneath it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 4:34 am
Clustered indexes define the logical order of the table
I would say: Clustered indexes define the physical order of the table. Am I right?
July 3, 2012 at 5:37 am
e-ghetto (7/3/2012)
Clustered indexes define the logical order of the table
I would say: Clustered indexes define the physical order of the table. Am I right?
No. There is no guarantee of physical order whatsoever. It's the logical order.
July 3, 2012 at 6:13 am
e-ghetto (7/3/2012)
Clustered indexes define the logical order of the table
I would say: Clustered indexes define the physical order of the table. Am I right?
No. They define the logical order only.
If a clustered index defined and guaranteed the physical order of data on disk, then clustered indexes would never become fragmented (as fragmentation is defined as the difference between physical and logical order). Is that the case?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 6:18 am
Physical order when index is built or rebuild, logical order thereafter until next rebuild of the cluster index. Kimberly tripp has nice articles on this and this topic is discussed in the master training vids. Also you can verify it by using a function called %%physloc%% which will show you the location on disk. So if you look at the table it will show you how the structure is stored on disk and you will notice it follows consecutively. BUt that only happens when the cluster index is built or rebuilt.
July 3, 2012 at 6:25 am
It's not even guaranteed after a create or rebuild. Try rebuilding an index and checking the fragmentation. If it's not exactly 0 then that freshly built/rebuilt index is still not in exact physical order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 7:02 am
GilaMonster (7/3/2012)
No. They define the logical order only.
Interesting! I'm in the business since five years - and all the time I've been wrong!:w00t:
Thanks!
July 3, 2012 at 9:55 am
GilaMonster (7/3/2012)
No. They define the logical order only.
The first time that you JOIN it won't matter anyway. There may be some ordering in intermediate result sets but the only thing getting benefit from that is the query processor and not you. Any ordering that you see in output results are happenstance. If you want it sorted use ORDER BY rather than relying on indexes.
By the way, I've been at this for over 30 years. There are days when I'm wrong. Not many but they happen.
ATBCharles Kincaid
July 3, 2012 at 11:02 am
Charles Kincaid (7/3/2012)
The first time that you JOIN it won't matter anyway.
Any time at all that you query the index's order is irrelevant. Join or no join.
I'm just sick of the myth that indexes enforce a physical order. It makes it hard to understand fragmentation and other aspects of index and query behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 4:40 pm
GilaMonster (7/3/2012)
Any time at all that you query the index's order is irrelevant. Join or no join.
Sorry. I misspoke (mistyped, whatever). In the absence of ORDER BY it’s happenstance what order the rows come back in. I have seen the same query, not having ORDER BY, produce different ordering and not even the underlying data changed.
ATBCharles Kincaid
July 3, 2012 at 5:03 pm
Charles Kincaid (7/3/2012)
In the absence of ORDER BY it’s happenstance what order the rows come back in. I have seen the same query, not having ORDER BY, produce different ordering and not even the underlying data changed.
Agreed. Nothing I've said in this article or elsewhere claims otherwise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 11:38 pm
GilaMonster (7/3/2012)
Any time at all that you query the index's order is irrelevant. Join or no join.
Hmm, when is the index's order relevant? I can create an index in ASC or DESC order:
CREATE NONCLUSTERED INDEX IX_PurchaseID ON dbo.Purchase(
PurchaseID DESC --descending order!
);
Will this "DESC" on PurchaseID make my query (potentially) faster if I want to select the latest purchase instead of the first?
July 4, 2012 at 1:51 am
e-ghetto (7/3/2012)
GilaMonster (7/3/2012)
Any time at all that you query the index's order is irrelevant. Join or no join.Hmm, when is the index's order relevant?
For seeking, for range scans, for supporting an order by. Not ever for returning rows in the index order without an order by specified. That was quoted a bit out of context (context being ordering of returned rows without an order by)
Will this "DESC" on PurchaseID make my query (potentially) faster if I want to select the latest purchase instead of the first?
I highly doubt it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 106 through 120 (of 124 total)
You must be logged in to reply to this topic. Login to reply