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 ««12

cluster index or non cluster index Expand / Collapse
Author
Message
Posted Thursday, May 13, 2010 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 4:15 AM
Points: 25, Visits: 170
Thanks for the clarification, I stand corrected
Post #921564
Posted Wednesday, May 26, 2010 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:08 AM
Points: 51, Visits: 162
I'm pleased to find it stated repeatedly here that the index design should always be specific to the situation, and tested for correctness.

I always object to the commonly held mantras that;
"In general every table should have a clustered index", "in general, the clustered index should be on the primary key" and "in general, clustered indexes perform better".

None of these things seem true to me. Or at least, the performance differences being discussed only apply in specific applications and not at all as a general rule.

In my experience, in the real world,

1. Most data (by volume) is entered and referenced sequentially - it's naturally sorted in the way it is entered in a heap.
2. Most data is not updated very much. The whole forwarding pointers thing is not a significant issue, especially if you're routinely maintaining your indexes.
3. By design, primary keys are less and less likely to be useful in semantic queries - there's a general design movement towards abstracting physical keys from the data so that we're using integers or GUID's as a physical key where a larger, logical semantic key could be built from the data also but would be i/o expensive to use. Primary keys are also essential in joins, or should be. A primary key should ideally be as small as possible (actually that may not always be true either, depends on the application).
4. Clustered indexes are most appropriate where the data is routinely manipulated in sorted batches rather than working with single rows. This is most commonly true of date ranged data, and most commonly in warehousing / olap / reporting applications.

5. I've just been going through the SQL 2008 70-432 exam course material. It states that

"A clustered index does not physically store the data on disk in a sorted order because doing so creates a large amount of disk input/
output (I/O) for page split operations. Instead, a clustered index ensures that the page chain of the index is sorted logically, allowing SQL Server to traverse directly down the page chain to locate data"

... I'm afraid I don't understand what sorting a page chain "logically" means.

Anyone?

Post #928482
Posted Wednesday, May 26, 2010 12:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 39,896, Visits: 36,242
reuben.anderson (5/26/2010)
... I'm afraid I don't understand what sorting a page chain "logically" means.


Each page in the index (and this goes for all indexes, not just clustered indexes) has, as part of the page header, the page number of the next and previous page in the index.

If you ever read the 'chose your own adventure' books, each story fragment in them has a next page to go to. You can kinda think of an index working that way. Read this page, then go to page 75 and read that, then to page 21 and read that.

If an index has no fragmentation, then logical and physical order match, and the 'next page' will always have a higher page ID than the current page.



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 #928529
Posted Thursday, May 27, 2010 1:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:08 AM
Points: 51, Visits: 162
Ah ok I see, so when necessary the "previous" and "next" index page pointers are updated.. the physical page isn't moved... a new row is inserted on correct physical data page, but due to fragmentation the data page is not necessarily in the correct physical order.

Cool. Thanks,

What do you think of my arguments earlier?
Post #928786
Posted Tuesday, February 15, 2011 11:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 2:10 AM
Points: 6, Visits: 63
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


Suvendu
Post #1064729
Posted Wednesday, February 16, 2011 1:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 39,896, Visits: 36,242
Suvendu (2/15/2011)
A clustered index is a special type of index that reorders the way records in the table are physically stored.


The clustered index enforces the logical storage order of the actual rows of the table, not the physical order. SQL will try, on create or rebuild, to make the two as similar as possible, but it is unlikely that they will remain that way

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.


A nonclustered index is a separate structure from the table, the nonclustered index key enforces the logical order of the index rows, not the physical order.

p.s. this thread is 4 years old.



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 #1064757
Posted Monday, October 10, 2011 3:58 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:19 AM
Points: 449, Visits: 882
Aaron Ingold (10/3/2007)
Boy, that's a question that could start forum wars...

First thing you have to understand is the difference between a clustered and nonclustered index. Clustered indexes define the physical sort order on disk of the whole table. Nonclustered indexes are seperate but connected to the table and store a small subset of the table, with pointers back to the location of the main table data.

More often than not, the clustered index is going to have better performance when it is used. Not always, because I've seen fully covered queries by nonclustered indexes that were able to be fulfilled faster using a more narrow (meaning fewer bytes allocated to columns) than by a full clustered index scan. But you want to have your clustered index cover the most commonly scanned, data. Usually that's also your primary key (which is why a clustered index is created by default on a PK in SQL Server), though not always.


Hi Aaron Ingold;

Does Clusterd index define the physical sort order?
Pls see the attached file(93.pdf) of MS press book's page of 'Self Passed TrainingKit Exam70-432'

Please clear my doubt.


Ali
MCTS SQL Server2k8


  Post Attachments 
93.pdf (3 views, 33.12 KB)
Post #1187793
Posted Monday, October 10, 2011 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 39,896, Visits: 36,242
Ali Tailor (10/10/2011)
Does Clusterd index define the physical sort order?


No, it does not. It defines the logical sort 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 #1187955
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse