locked
CLUSTERED or NONCLUSTERED index, which one is better? RRS feed

  • Question

  • There are some big tables in database. We have to provide recommendation to application team to create index on some column of the tables. What kind of index should we recommend? CLUSTERED & NONCLUSTERED index? which one is better? 
    Monday, November 28, 2016 5:16 PM

Answers

  • See also MSDN Clustered and Nonclustered Indexes Described for more details.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 29, 2016 6:33 AM
    Answerer
  • There are some big tables in database. We have to provide recommendation to application team to create index on some column of the tables. What kind of index should we recommend? CLUSTERED & NONCLUSTERED index? which one is better? 
    Both are good, you are trying to ask question which cannot be answered in simple discussion. Bothe indexes have their task and both are good. Ideally a table should have a clustered index but in many cases a table is just a heap and with many non clustered indexes.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, November 29, 2016 7:04 AM
    Answerer
  • Hi Arif,

    Some times even Estimated or Actual execution plan will suggest you on required indexes.

    Thanks

    M.Ramesh


    Ramesh. M

    Tuesday, November 29, 2016 9:01 AM
  • "n summary there is only one index per table of type CLUSTERED, this is already the primary key,"

    Just to expand a bit on above:

    A primary key *defaults* to a clustered index. But whoever created the PK (designed the table) could define the PK with a non-clustered index. I.e., it isn't mandatory to have the PK with a clustered index.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 29, 2016 9:57 AM
  • Hi,

    Index creation is dependent on the table and query needs.

    it is always there is only one clustered index per table, so people normally use a clustered index with other types of indexing. 

    So the answer it depend.

    If you need to know more deep details I would recommend Index internals

    http://www.sqlskills.com/blogs/kimberly/indexes-in-sql-server-20052008-part-2-internals/

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Tuesday, November 29, 2016 10:15 AM
  • Hi arifulhaq,

    If I understand this correctly, you could take a look into the index creation script described in this blog

    For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 10:34 AM

All replies

  • n summary there is only one index per table of type CLUSTERED, this is already the primary key,
    So if there is already data in the table, you can only suggest indexes NONCLUSTERED

    Remember that approaching just creating an index will not necessarily solve the problem of performace

    You need to do a broad analysis of your context.

    Is there a script that sujere indices in sql server

    In many large tables

    1) use index in filter fields (where)

    2) in very large tables I like to create NONCLUSTERED indexes on top of the FKS

    Wesley Neves

    Monday, November 28, 2016 5:38 PM
  • It really depends on the query that runs  along with WHERE condition and etc. BTW by definition both indexes are different

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 29, 2016 6:06 AM
  • See also MSDN Clustered and Nonclustered Indexes Described for more details.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 29, 2016 6:33 AM
    Answerer
  • There are some big tables in database. We have to provide recommendation to application team to create index on some column of the tables. What kind of index should we recommend? CLUSTERED & NONCLUSTERED index? which one is better? 
    Both are good, you are trying to ask question which cannot be answered in simple discussion. Bothe indexes have their task and both are good. Ideally a table should have a clustered index but in many cases a table is just a heap and with many non clustered indexes.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, November 29, 2016 7:04 AM
    Answerer
  • Read info

    Many Thanks & Best Regards, Hua Min

    Tuesday, November 29, 2016 7:47 AM
  • Hi Arif,

    Some times even Estimated or Actual execution plan will suggest you on required indexes.

    Thanks

    M.Ramesh


    Ramesh. M

    Tuesday, November 29, 2016 9:01 AM
  • "n summary there is only one index per table of type CLUSTERED, this is already the primary key,"

    Just to expand a bit on above:

    A primary key *defaults* to a clustered index. But whoever created the PK (designed the table) could define the PK with a non-clustered index. I.e., it isn't mandatory to have the PK with a clustered index.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, November 29, 2016 9:57 AM
  • Hi,

    Index creation is dependent on the table and query needs.

    it is always there is only one clustered index per table, so people normally use a clustered index with other types of indexing. 

    So the answer it depend.

    If you need to know more deep details I would recommend Index internals

    http://www.sqlskills.com/blogs/kimberly/indexes-in-sql-server-20052008-part-2-internals/

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Tuesday, November 29, 2016 10:15 AM
  • Hi arifulhaq,

    If I understand this correctly, you could take a look into the index creation script described in this blog

    For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 10:34 AM
  • Clustered index should be better because in cluster index we can stored data with physical order like a hard disk but in non clustered index data should be scatter. 

    Sort is easy in clustered index. My opinion is clustered index is better.

    Friday, January 19, 2018 6:43 AM