You must Sign In to post a response.
  • Category: SQL Server

    Difference between clustered index and nonclustered

    Can any one tell me difference between clustered index and nonclustered index with diagram
  • #652790
    Clustered Index

    Only one per table
    Faster to read than non clustered as data is physically stored in index order
    Non Clustered Index

    Can be used many times per table
    Quicker for insert and update operations than a clustered index
    Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

    Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

    SQL Server will normally only use an index if its selectivity is above 95% (ie 95% of the records are unique values).

  • #652802
    A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don't need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.


    On other hand you can create as many as non-clustered index you reqired on the table but it is advisible to not to create more than 4-5 non-clustered index per table otherwise it will effect the performance of table

  • #652822
    A clustered index is a special type of index that reorders the way in which records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM

  • #652854
    Hello,

    The clustured index generate automatically when you create a primary key on a table. The Clusturd index basically re order the database table data. We can have only one clusured index for a table. According perfomance wise it is littlebit slower when we do DML operations across the table.

    The Non Clustured index is maintans the logical order and can create multiple upto 999 clustured Indexers per table.

    Regards,
    Naveen


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.