SQL Server Performance

CLUSTERED Vs NON-CLUSTERED

Discussion in 'T-SQL Performance Tuning for Developers' started by pavankan, Jan 6, 2004.

  1. pavankan New Member

    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.

  2. FrankKalis Moderator

    Yes, that's a nasty feature of SQL Server <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br />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.<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  3. ChrisFretwell New Member

    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
  4. FrankKalis Moderator

  5. FrankKalis Moderator

  6. pavankan New Member

    Thanks for replying!<br /><br />Is the clustered index with one col more heavier to maintain than a non-clustered with one col?<br /><br />What I am asking is, is it any different at all?<br /><br />Infact, if it is not I'd like to avaoid doing it.<br /><br />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=':(' /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Yes, that's a nasty feature of SQL Server <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br />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.<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.<br /><br />
  7. FrankKalis Moderator

    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
  8. pavankan New Member


    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.

  9. Luis Martin Moderator

    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
  10. FrankKalis Moderator

    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
  11. Twan New Member


    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
  12. FrankKalis Moderator

  13. Luis Martin Moderator

    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
  14. FrankKalis Moderator

  15. Luis Martin Moderator

    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
  16. pavankan New Member

    Not really! Me the 'original questioner' did have the 'physical implementation' in mind.<br />I am trying to assimilate the info in the article I was reading:<br /><br />------------------------------------------------------------<br />An Introduction to Clustered and Non-Clustered<br />Index Data Structures<br />by G. Vijayakumar<br />2 December 2003 <br />AT:<br /<a target="_blank" href=http://www.sql-server-performance.com/gv_index_data_structures.asp>http://www.sql-server-performance.com/gv_index_data_structures.asp</a><br />------------------------------------------------------------<br /><br />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.<br /><br />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=':)' />]<br /><br />The thing is we have a huge govt owned application whose back end is SQL. We are doing everything possible to improve its performance. <br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Okay, now we're going into the storage engine and the physical implementation.<br />I don't think that the original questioner has this in mind, or?<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.<br /><br />
  17. FrankKalis Moderator

    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.<br /><br />It's really worth the money. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  18. pavankan New Member

    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.<br /><br />Thanks for the suggestion; I'll see if my boss would buy it![<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />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.<br /><br />It's really worth the money. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.<br /><br />
  19. FrankKalis Moderator

    I don't know the book you've mentioned, but IIRC the book by Delaney is the official reference. <br />There are many books on SQL Server around, but only a few really worth reading. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Good luck with your boss!<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  20. Twan New Member


    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
  21. ChrisFretwell New Member

    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
  22. FrankKalis Moderator

    Hi Twan,<br />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.<br />Also I wanted to add this thread because of the external links provided there <br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2720>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2720</a><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  23. Twan New Member

    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=';-)' /> )<br /><br />Cheers<br />Twan
  24. FrankKalis Moderator

    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=':)' />]<br /><br />....and I dont get provision for marketing it [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  25. gaurav_bindlish New Member

    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
  26. Twan New Member

    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
  27. bambola New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br />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=';-)' /> )<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />You must get this book Twan, it's amazing. Kalen Delaney is The SQL Goddess! <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Bambola.
  28. FrankKalis Moderator

Share This Page