When to use a clustered and/or non-clustered index

  • itmasterw 60042

    SSCrazy

    Points: 2528

    Hi,

    I have been looking all over the net and in books to findwhen to use a clustered and/or non-clustered index; and all I get isdefinitions, not when to use one or both. Can you please tell me how I canlearn this, or would you be able to give me some general steps to do this?

    Thank you

  • Sean Lange

    SSC Guru

    Points: 286363

    itmasterw 60042 - Thursday, August 3, 2017 9:23 AM

    Hi,

    I have been looking all over the net and in books to findwhen to use a clustered and/or non-clustered index; and all I get isdefinitions, not when to use one or both. Can you please tell me how I canlearn this, or would you be able to give me some general steps to do this?

    Thank you

    The stairways article series right here on this site is absolutely fantastic. http://www.sqlservercentral.com/stairway/72399/
    Once you fully understand indexes it becomes second nature if a given index should be clustered or nonclustered.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Luis Cazares

    SSC Guru

    Points: 183496

    itmasterw 60042 - Thursday, August 3, 2017 9:23 AM

    Hi,

    I have been looking all over the net and in books to findwhen to use a clustered and/or non-clustered index; and all I get isdefinitions, not when to use one or both. Can you please tell me how I canlearn this, or would you be able to give me some general steps to do this?

    Thank you

    Clustered indexes should be used always. You can read some recommendations about it in here: http://www.scarydba.com/2011/04/04/sql-universityrecommendations-for-a-clustered-index/ . (Be sure to read the links in the conclusion)
    Be careful when designing them as they're the base for performance tuning and other features.
    Non-clustered indexes are used as an additional performance enhancement to retrieve data (or validate constraints). In my opinion, you should never have a non-clustered index without a clustered index. If you understand how each index works, you'll find it easier to understand when to use them.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • itmasterw 60042

    SSCrazy

    Points: 2528

    Luis Cazares - Thursday, August 3, 2017 9:45 AM

    itmasterw 60042 - Thursday, August 3, 2017 9:23 AM

    Hi,

    I have been looking all over the net and in books to findwhen to use a clustered and/or non-clustered index; and all I get isdefinitions, not when to use one or both. Can you please tell me how I canlearn this, or would you be able to give me some general steps to do this?

    Thank you

    Clustered indexes should be used always. You can read some recommendations about it in here: http://www.scarydba.com/2011/04/04/sql-universityrecommendations-for-a-clustered-index/ . (Be sure to read the links in the conclusion)
    Be careful when designing them as they're the base for performance tuning and other features.
    Non-clustered indexes are used as an additional performance enhancement to retrieve data (or validate constraints). In my opinion, you should never have a non-clustered index without a clustered index. If you understand how each index works, you'll find it easier to understand when to use them.

    Okay thanks I will look at this
    Thank you

  • Luis Cazares

    SSC Guru

    Points: 183496

    This is also a good place to start.
    http://www.sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply