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 «««910111213»»

Introduction to Indexes Expand / Collapse
Author
Message
Posted Tuesday, July 3, 2012 6:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1324333
Posted Tuesday, July 3, 2012 6:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:47 AM
Points: 22, Visits: 214
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.
Post #1324341
Posted Tuesday, July 3, 2012 6:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1324346
Posted Tuesday, July 3, 2012 7:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:20 AM
Points: 201, Visits: 228
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!

Thanks!
Post #1324368
Posted Tuesday, July 3, 2012 9:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #1324472
Posted Tuesday, July 3, 2012 11:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1324532
Posted Tuesday, July 3, 2012 4:40 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #1324734
Posted Tuesday, July 3, 2012 5:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1324738
Posted Tuesday, July 3, 2012 11:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:20 AM
Points: 201, Visits: 228
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?
Post #1324790
Posted Wednesday, July 4, 2012 1:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1324840
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»

Permissions Expand / Collapse