SQL Server Performance Forum – Threads Archive

CLUSTERED Vs NON-CLUSTERED

When we create a ‘clustered’ index with just one cloumn, isn’t it just the same as a ‘non-clustered’ index? SQL 2000, creates a clustered index as a defalut on every primary key constraint Would it do any good if we’d make each of those automatically created ‘clustered’ indexes ‘non-clustered’ ones? Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

Yes, that’s a nasty feature of SQL Server <img src=’/community/emoticons/emotion-6.gif’ alt=’

Can you show where you read that sql 2000 created a clustered index on every primary key? This would be news to me. Plus, a clustered index is not the same as an non-clustered index. Clustered indexes sort and store the data in order. From BOL
Clustered
Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key, clustered indexes are efficient for finding rows. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. The data rows themselves form the lowest level of the clustered index. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.
PRIMARY KEY creates a unique index to enforce the primary key.
Chris

Sorry, forgot:
A clustered index with even one column isn’t the same as a nonclustered index, because SQL Server establishes the page chain only with clustered indexes. Frank
http://www.insidesql.de
http://www.familienzirkus.de

Hi Chris,
try and create a table with EM and you should see Frank
http://www.insidesql.de
http://www.familienzirkus.de

Thanks for replying! Is the clustered index with one col more heavier to maintain than a non-clustered with one col? What I am asking is, is it any different at all? Infact, if it is not I’d like to avaoid doing it. Our db has 117 tables with this default clustered index on the primary key. I think it will be a loooooong process to drop each PK constraint, drop the index and recreate a PK constraint with a non-clustered index.. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Yes, that’s a nasty feature of SQL Server <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> While this is commonly better than not having a clustered index at all, you should make it non-clustered. And since you can only have one clustered index per table, ‘save’ it for something more useful. Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

I don’t think that’s it’s more heavier to maintain. All SQL Server has to do is to mark in the index pages the previous and the next page to maintain the page chain of a clustered index. Yes, it should be a pain to wade through your db and correct it, but the advantage of having a useful clustered index outweights the labour by far.
Btw, when creating clustered indexes specify always with UNIQUE keyword. This way SQL Server does not have to add a uniqueifier, which is wasted storage space. Have you searched this site for indexes in general. Should return some very useful articles. Frank
http://www.insidesql.de
http://www.familienzirkus.de

A table is stored as a heap only when there is no index at all.
A table is stored in the sorted order even when there is a non-clustered index on it. Please correct me if I am wrong. I picked that up from this: ————————————————————
An Introduction to Clustered and Non-Clustered
Index Data Structures
by G. Vijayakumar
2 December 2003
AT:
http://www.sql-server-performance.com/gv_index_data_structures.asp
————————————————————

quote:Originally posted by ChrisFretwell Can you show where you read that sql 2000 created a clustered index on every primary key? This would be news to me. Plus, a clustered index is not the same as an non-clustered index. Clustered indexes sort and store the data in order. From BOL
Clustered
Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key, clustered indexes are efficient for finding rows. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. The data rows themselves form the lowest level of the clustered index. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.
PRIMARY KEY creates a unique index to enforce the primary key.
Chris

Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

What you read is OK.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

quote:Originally posted by pavankan
A table is stored in the sorted order even when there is a non-clustered index on it. Please correct me if I am wrong. I picked that up from this:
————————————————————
An Introduction to Clustered and Non-Clustered
Index Data Structures
by G. Vijayakumar
2 December 2003
AT:
http://www.sql-server-performance.com/gv_index_data_structures.asp
————————————————————

It is NOT true. A table is not physically stored in a sorted order according to some index. Even a clustered index does not store the pages in a sorted manner.
If SQL Server would do so, this would make maintainance far too expensive and inefficient. What is done is, that by creating a clustered index, the so-called page chain is established. That is, each data page (the leaf level of a clustered index is the data itself) contains information about previous and next data page. This way maintenance is easier and faster.
Sorting or ordering is somewhat irrelevant in a RDBMS. And the only reliable way to force SQL Server to bring back an ordered resultset is by using the ORDER BY keyword Frank
http://www.insidesql.de
http://www.familienzirkus.de

yes, the index itself is sorted for both clustered and non-clustered indexes, but the data is only sorted according to the clustered index. and even with a clustered index the data in the table is only sort of sorted. Within a single page the data need not be sorted, which eases clustered index maintenance by an order of magnitude. also in terms of PK automatically being clustered indexes, this is not a default of SQL, but a default of EM. and to be honest EM is a nasty feature of SQLServer… It is not a development or database design tool and shouldn’t be used for this purpose… Cheers
Twan

Hmmm…
According to which criteria would a nonclustered index be sorted? Frank
http://www.insidesql.de
http://www.familienzirkus.de

My oppinion in short: 1) Can create any tables without index or pk at all.
2) if 1) data are stored in order, the order is according each row was inserted, of course no specific order of any column. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Okay, now we’re going into the storage engine and the physical implementation.
I don’t think that the original questioner has this in mind, or? Frank
http://www.insidesql.de
http://www.familienzirkus.de

Take it easy man, this is not the first post begining with one topic and go on with others.
In fact you were right when you correct my answer to second part of the question, that was the razon of my last post, to clarify my misstake.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Not really! Me the ‘original questioner’ did have the ‘physical implementation’ in mind. I am trying to assimilate the info in the article I was reading: ———————————————————— An Introduction to Clustered and Non-Clustered Index Data Structures by G. Vijayakumar 2 December 2003 AT: http://www.sql-server-performance.com/gv_index_data_structures.asp</a> ———————————————————— and was trying to assess the situation wether to undertake the drudgery of changing all the default primary key clustered indexes to non-clustered ones. Thank you all! I am slowly gaining an insight into this very exciting realm of SQL performance. I am game to more of these discussions![<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] The thing is we have a huge govt owned application whose back end is SQL. We are doing everything possible to improve its performance. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Okay, now we’re going into the storage engine and the physical implementation. I don’t think that the original questioner has this in mind, or? Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

I would spent some $40 on a copy of Inside SQL Server 2000 by Kalen Delaney. This is the technical reference for the internals of SQL Server. It’s really worth the money. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

I have ‘Microsoft SQL Server 2000 Perfomance Optimization and Tuning Handbook’ by Ken England. I think it kinda begins to talk about stuff and stops short of actually giving enough for me to go do somethihng on the db with confidence. Thanks for the suggestion; I’ll see if my boss would buy it![<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I would spent some $40 on a copy of Inside SQL Server 2000 by Kalen Delaney. This is the technical reference for the internals of SQL Server. It’s really worth the money. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

I don’t know the book you’ve mentioned, but IIRC the book by Delaney is the official reference. There are many books on SQL Server around, but only a few really worth reading. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Good luck with your boss! Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

all indexes are stored sorted based on the columns specified in the index. They use a btree structure to point to link the pages in the tree, one caveat to this is that the bottom level of a clustered index (i.e. the data pages) are not stored exactly sorted. The first page will have records that come before the second page, but rows within a data page are not stored in a sorted order. This is to minimise having to move rows when a new row is inserted. So scanning the leaf nodes of a clustered index yields a nearly-sorted result set, whereas scanning the leaf nodes of a non-clustered index yields a sorted result set. Cheers
Twan

Ah, so EM is the culprit, not SQL. I didnt think it was a SQL thing to auto create a clustered. Since I came from the world of sql pre-EM (and during horrible 6.0 EM), I dont use it for much, and still to prefer scripting most things. As for clustered indexes, or any index for that matter, if its well thought out and designed to meet the needs of the application then its not heavier to maintain than not having one. Almost all literature says to have a clustered index on every table. A good easy read on this and how to help pick a data item for the clustered is found at this site http://www.sql-server-performance.com/clustered_indexes.asp
Kalen is an amazing reference for anything sql related. Chris

Hi Twan, thanks for getting me flipping through the pages on Inside SQL Server again [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Found the relevant parts. Also I wanted to add this thread because of the external links provided there http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2720</a> Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

people keep talking about Kalen’s book… I’ll have to actually get it and read it some time… (although working on upgrading my mcse first… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> ) Cheers Twan

I highly recommend it. Most books on SQL Server have some interesting stuff and a lot of boring stuff. This one is interesting from beginning to end [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ….and I dont get provision for marketing it [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

From what I remember from Inside SQL Server 2K is that SQL Server by default creates clustered index on primary key column unless specified otherwise. Which means that if you specify non-clusetered index in the index creation statement, SQL Server will create non-clustered and not clustered index… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ah yes you are right Gaurav. A primary key constraint does default to clustered, unless there is already a clustered index… Its standard indexes that default to non-clustered… Cheers
Twan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Twan people keep talking about Kalen’s book… I’ll have to actually get it and read it some time… (although working on upgrading my mcse first… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> ) <hr height="1" noshade id="quote"></blockquote id="quote"> You must get this book Twan, it’s amazing. Kalen Delaney is The SQL Goddess! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Bambola.

Nah, there are better books on programming SQL Server, but nothing beats the book on the internals behind the scene. Frank
http://www.insidesql.de
http://www.familienzirkus.de

]]>