Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: put all columns in one index or create one index per column

    Hi,
    For reach best performance, its better to put all columns in one index or create one index per column that must have index ?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Farax x - probably neither.
    It sounds like you don't know too much about indexes but have heard that having some "speeds things up". it is also true that having some indexes can "slow things down".

    I would either read up on indexing in SQL Server, or run all you queries against your database and then run the Database Tuning Advisor to find out what queries are recommended. The former is the better plan, the latter is quick, dirty and cheap but won't give you an optimal set up.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    None of above / both / one of above.

    depending upon your table structure , data , NOT NUMBER OF ROWS, relation , dml .

    best performance comes with no index in sql-server ( unlinke oracle)
    and when needed adding up an index to the column as per required by the query .

    but now you have an option of index-referencing in select statement . This gives you a robust option to index your query according to your specification.

  4. #4
    Join Date
    Sep 2009
    Posts
    16
    thanx alot for your answer
    Quote Originally Posted by mishaalsy View Post
    None of above / both / one of above.

    depending upon your table structure , data , NOT NUMBER OF ROWS, relation , dml .

    best performance comes with no index in sql-server ( unlinke oracle)
    and when needed adding up an index to the column as per required by the query .

    but now you have an option of index-referencing in select statement . This gives you a robust option to index your query according to your specification.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mishaalsy View Post
    best performance comes with no index in sql-server
    Come again?
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    Quote Originally Posted by mishaalsy View Post
    best performance comes with no index in sql-server
    Come again?
    He said the best performance comes with no index in sql-server.
    Wait...what?
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by farax_x View Post
    Hi,
    For reach best performance, its better to put all columns in one index or create one index per column that must have index ?
    Depends on how you are going to search your data.
    If you are going to join or filter on one column at a time, create separate indexes.
    If you are usually going to join or filter on multiple columns (often the case if you are using natural keys rather than surrogates), then use a composite index.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...but the nature of those joins and the nature of those filters, as well as the nature of the data, are also considerations on whether or not you index those columns. There is also the whole clustered Vs nonclustered issue.
    I think the OP is of too much an uninformed mind to really be able to make much of that advice.

    And I couldn't make sense of much mishaalsy said
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Index Intercourse....ummm...intersection!!!
    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.

  10. #10
    Join Date
    Sep 2009
    Posts
    16
    Quote Originally Posted by pootle flump View Post
    Farax x - probably neither.
    It sounds like you don't know too much about indexes but have heard that having some "speeds things up". it is also true that having some indexes can "slow things down".

    I would either read up on indexing in SQL Server, or run all you queries against your database and then run the Database Tuning Advisor to find out what queries are recommended. The former is the better plan, the latter is quick, dirty and cheap but won't give you an optimal set up.
    sorry, may be i explain my question badly.I have a table with 10 million records, and i created Separate index for each index-needed columns . my question is :
    its better put all columns together in one index or this situation is the best.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Again, it depends. I'm afraid there is no "oh it's best to have them all in one column" or "it's best to have them all in different columns".

    Indexing is difficult if you don't know a great deal about it but easy when you do. You would be best to read the BoL entries on indexing and then try asking something more specific.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2009
    Posts
    16
    Quote Originally Posted by pootle flump View Post
    Again, it depends. I'm afraid there is no "oh it's best to have them all in one column" or "it's best to have them all in different columns".

    Indexing is difficult if you don't know a great deal about it but easy when you do. You would be best to read the BoL entries on indexing and then try asking something more specific.
    can u tell me, when should put several column in one index ?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Want to post the DDL and some sample queries you are using?
    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.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by farax_x View Post
    can u tell me, when should put several column in one index ?
    Uhm....I already did. Read my post.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, besides all the fun and games...

    Understand 1 thing

    Only the 1st column will make the optimizer decide what index to use

    So, putting all the columns in an index, lets say from a phonebook table is useless, if the list of columns are phone_num, Last_name, first_name, ect

    AND you were looking up by last name

    it would NEVER use that index and would scan...it MIGHT scan the index depending on the number of columns in your select vs. the columns in the table, vs. the columns in the index

    So you see....we need to know what the barrel of data is, and how you want to go after it

    There's some exotic stuff like merry-go-round scans...but don't think about

    The Index Intersection thing however might be beneficial

    But you can do some reading

    Understanding SQL Server Indexing

    SQL Server Index Basics

    General Tips on Optimizing SQL Server Indexes

    SQL Server Indexes: The Basics - SQLTeam.com
    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.

Tags for this Thread

Posting Permissions

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