Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Red face Unanswered: General notion about Clustered Index

    Hi,
    Though I know Clustered index is done on the field which have the greater possibilities of repeatition of data,I often come across some sample projects where clustered index have been done in primary key. And most of the cases have the unique id as the primary key field.I think that is a wrong idea.Most of the people are using the clustered index in a wrong way.Bcoz Non clustered index is used to index the fields which have unique data. Plz suggest am I right or wrong.....
    Thanks!!
    Joydeep

  2. #2
    Join Date
    Jan 2006
    Posts
    3
    I don't have that much experience, i migth be worng, but

    I believe the main diference betwen non-clustered and clustered index, is the fact that in clustered indexes the closest values are fisically closer(that's why u cant have more then 1 clustered index per table)
    Making inserting slower (inserting may move all the data around), but also making sorting faster and indexes smaller.
    using clustered index or simple index depends on the use of the table.
    if you have a large table that is static but you use it very often, its a good idea to have it clustered in the primary key (the one you use on join's), making table access faster throw the primary key.
    If you're always changing data, it will slow down the update/insert
    You have to compromise.

    again, i migth be wrong (it may all fall down if I'm wrong about the clustered/non-clustered diference), I would like to see the answer from someone with more experience

    edit - the diference between clustered/non-clustered index probably isn't quite understood, with that obviosly there's people who use it wrong

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A clustered index physically sorts the data on the pages in that order. PK's by there very nature will be unique, thus having the highest level of cardinaliy of the table.

    Non Clustered indexes can be unique or not unique.

    I'm sorry, what was the question again?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Maybe some examples would be of help:

    It would be a horrible idea to place a clustered index on a field that is the Primary Key defined as uniqueidentifier with a default value of NEWID(). (see note 1)

    A clustered index on an auto-incrementing IDENTITY field may be useful depending on how the data in the field is being accessed. If it is being accessed in large ranges (ie, WHERE ID BETWEEN x AND y), then it's a useful thing to have. If the ID is part of a foreign key relationship that is frequently invoked in parent-child table joins, then it's a useful thing.

    If it's defined on a stand-alone table (no relationships) and the data is accessed atomically (ie, WHERE ID = x) and frequent updates are made along with frequent inserts AND the data is simultaneously being accessed in ranges by another field, then there MAY be an opportunity to move the clustered index to another field (ie, a date field used for accessing ranges of data such as WHERE MyDateField BETWEEN x/x/xxxx and y/y/yyyy.

    As Brett stated, a clustered index physically orders the data on the drive; inserts will be placed in the proper order which may mean re-ordering the data frequently (consider a replication scenario where each subscriber has it's own "range" of identitiy values).

    Bottom line:
    1. know what a clustered index is,
    2. know what it does to your data,
    3. recognize that MS default values/settings (ie PKs default to clustered) may not be right in all situations, and
    4. Test, test, test, test

    Your mileage may vary, prices exclude taxes, tags, title, Lot number #244518, not all buyers may qualify, see dealer for additional details.

    Regards,

    hmscott

    Note 1: Real Life (TM) horror scenario. And it's a vendor-managed db about which I can do doodly.
    Have you hugged your backup today?

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by hmscott
    Bottom line:
    1. know what a clustered index is,
    2. know what it does to your data,
    3. recognize that MS default values/settings (ie PKs default to clustered) may not be right in all situations, and
    4. Test, test, test, test

    Your mileage may vary, prices exclude taxes, tags, title, Lot number #244518, not all buyers may qualify, see dealer for additional details.

    Regards,

    hmscott

    Hi Hmscott,
    firstly thanks for your valuable comments.
    Well,I do know the difference between Clustered and non clustered index.
    I have used them frequently for faster query performance in Land Record in West Bengal,India.And I also have those info that each table can have only one clustered index and 249 non clustered index max.
    But My question was why MS has made the primary key Clustered index by default?
    Thats making the confusion greater among the people.
    Now I got the answer right for me.
    Thanks again for those words.
    Joydeep

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Brett Kaiser
    A clustered index physically sorts the data on the pages in that order. PK's by there very nature will be unique, thus having the highest level of cardinaliy of the table.

    Non Clustered indexes can be unique or not unique.

    I'm sorry, what was the question again?
    Hi Brett,
    My question was why MS has made the primary key clustered index by default? GENERALLY THATS MAKING THE problem ...I mean people are getting confused...
    Plz comment on this
    Thanks!!
    Joydeep

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    By default?

    This means you are using Enterprise Manager.

    I use Query Analyzer exclusively to build my objects.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by rudra
    But My question was why MS has made the primary key Clustered index by default?
    Note meaning to be rude, but I read through dozens of e-mails per day, analyze some SQL/T-SQL, handle support calls, hold my developers' hands through ugly SQL code and work with my manager to plan the coming year's budget.

    If you've got a question, put it up front! Bolding it like you did the second time 'round helps to. Paragraphs and bullet points also help; it shows me that you've taken the time to organize your thoughts.

    As for PKs defaulting to clustered indexes, if I had a nickle for every bad default setting that M$ has, has had or will have, I would be a very wealthy person indeed. On the other hand, if I had to fill in every setting, parameter, variable with a value of my own, with no prompting or default value, I would probably never touch another M$ product again.

    There are tradeoffs to be made; you want a better product? Get on the the beta testing team, or get on the design team, or ... build your own.

    Again, not meaning to be rude, but this just isn't an issue big enough to consume valuable CPU cycles (mine or yours).

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thank you very much Hmscott.Thanks a lot for those wonderful comments.
    Joydeep

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's just like anything, you have to know the product. For example, in DB2, creating a primary key does not generate an index. However the first index created (be it for the PK or not), is the clustering index.

    Scenario 2, for example, in DB2 would create that as a clustered index. In sql server it does not. You can force the DB2 Paradigm in the 3rd example.

    It bugs me more that SQL server will not allow more than 1 null for a unique index. But there are always work arounds.

    http://weblogs.sqlteam.com/brettk/ar...4/20/4592.aspx

    But just like anything, confusion is based on a lack of knowledge or perseverance. You've spent more energy arguing the "point" than doing research. What is, is.

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), PRIMARY KEY(Col1))
    GO
    
    sp_help myTable99
    GO
    
    DROP TABLE myTable99
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
    CREATE INDEX myIndex99 ON myTable99(Col2)
    ALTER TABLE myTable99 ADD PRIMARY KEY(Col1)
    GO
    
    sp_help myTable99
    GO
    
    DROP TABLE myTable99
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
    CREATE UNIQUE CLUSTERED INDEX myIndex99 ON myTable99(Col2)
    ALTER TABLE myTable99 ADD PRIMARY KEY(Col1)
    GO
    
    sp_help myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think he was arguing, just seeking enlightenment in a Socratic manner.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Brett Kaiser

    But just like anything, confusion is based on a lack of knowledge or perseverance. You've spent more energy arguing the "point" than doing research.
    hi Brett,
    you are right.I am not an expert in SQL SERVER 2000 but a novice in this field.And hence lack of knowledge and perseverance is quite natural ...
    These limitations forced me to post the issues to u guys who are very much knowledgable and skilled in this subject.
    I think due to this limitations my arguements sometimes sounds baseless.But that was not my intention.From now onwards I will try to make them more meaningful.
    As u said, "MUST DO MORE RESEARCH ON ISSUES" I will definetly do so.
    Thank you very much for your kind words.
    Thanks!!
    Joydeep

  13. #13
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Thank you very much Brett

    hi Brett,
    you are right.I am not an expert in SQL SERVER 2000 but a novice in this field.And hence lack of knowledge and perseverance is quite natural ...
    These limitations forced me to post the issues to u guys who are very much knowledgable and skilled in this subject.
    I think due to this limitations my arguements sometimes sounds baseless.But that was not my intention.From now onwards I will try to make them more meaningful.As u said, "MUST DO MORE RESEARCH ON ISSUES" I will definetly do so.
    Thank you very much for your kind words.
    Thanks!!
    Joydeep

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I don't think he was arguing, just seeking enlightenment in a Socratic manner.

    So much for Socratic...

    I still don't see the "limitations". I gave you some very clear cut examples how to do everything you could possibly want.

    And as for lack of knowledge, do you have any of the sql client tools installed? Do you know what Books online is? If you don't have them, then M$ does. And most of the times the answers are right there. In any case a quick google will tell you.

    You appeared to be debating that the manner the DDL operates is incorrect, or at least "confusing". OK, but so what. It's just the way it operates.

    And the majority of the time it's not a bad thing. It's when IDENTITY is so oftem used that certain problems make themselves apparanet, but you need to be have that discussion on a massive scale.

    Don't debate things, just because...it gets very boring.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Brett Kaiser
    So much for Socratic...

    I still don't see the "limitations". I gave you some very clear cut examples how to do everything you could possibly want.

    And as for lack of knowledge, do you have any of the sql client tools installed? Do you know what Books online is? If you don't have them, then M$ does. And most of the times the answers are right there. In any case a quick google will tell you.

    You appeared to be debating that the manner the DDL operates is incorrect, or at least "confusing". OK, but so what. It's just the way it operates.

    And the majority of the time it's not a bad thing. It's when IDENTITY is so oftem used that certain problems make themselves apparanet, but you need to be have that discussion on a massive scale.

    Don't debate things, just because...it gets very boring.
    ok Man,
    I think its over now .Let us end this issue.I think there are more queries to be answered,just not this only.It seemed to be the only post in the forum.
    I think I have thanked everybody for those comments already several times.
    Now its getting boring for me to thank again and again.So stop posting replies to his issue and move on.Why can't we just try to concentrate on isssues rather than individuals? Just don't get personal......

    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •