cluster index or non cluster index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » cluster index or non cluster index

18 posts, Page 2 of 2

««1 2

cluster index or non cluster index

Rate Topic

Display Mode

Topic Options

Author

Message

tony.turner

Posted Thursday, May 13, 2010 12:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:06 AM
Points: 25, Visits: 155

Thanks for the clarification, I stand corrected

Post #921564

reuben.anderson

Posted Wednesday, May 26, 2010 11:23 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 03, 2013 8:04 AM
Points: 27, Visits: 136

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

GilaMonster

Posted Wednesday, May 26, 2010 12:27 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

reuben.anderson

Posted Thursday, May 27, 2010 1:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 03, 2013 8:04 AM
Points: 27, Visits: 136

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

Suvendu

Posted Tuesday, February 15, 2011 11:13 PM

Forum 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 Smile

Post #1064729

GilaMonster

Posted Wednesday, February 16, 2011 1:11 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Ali Tailor

Posted Monday, October 10, 2011 3:58 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, October 25, 2012 2:19 AM
Points: 394, Visits: 869

Aaron Ingold (10/3/2007)

Boy, that's a question that could start forum wars... Wink

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

GilaMonster

Posted Monday, October 10, 2011 8:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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 »

18 posts, Page 2 of 2

««1 2

Permissions