What is the difference between a clustered and non-clustered index in MS-SQL?

Posted on 2012-09-02
Last Modified: 2012-09-02

I know I've seen multiple posts of the answer to this question, but what is the answer in straightforward, easy to understand terms.

What is the difference between a clustered and non-clustered index in MS-SQL?

Question by:jxbma
    LVL 12

    Accepted Solution

    Clustered index: The rows are stored physically on the disk in the same order as the index in database. therefore you can have only one clustered index for a table.
    Reading is faster while writing to a table with a clustered index can be slower.

    Non clustered index: Only has pointers to the physical rows, so they aren't physically sorted on the disk. you can have many non-clustered indexes on a table; each new index will increase the time it takes to write new records
    LVL 12

    Expert Comment

    A clustered index is something that physically determines the organization of records in any table. Example: a language dictionary where physical organization of all words is based on alphabets in those words starting left to right. That's why husband stands behind foolishness and wife appears before wisdom in the dictionary. :)

    On the other hand non clustered index is a separate structure that helps you traverse through records in the table like an index of a book that may tell you that chapter number 7 of that book starts at page 86 and ends at page 104.

    That is the reason why we could have only one clustered but multiple non clustered indexes within a table.

    Following is one link that might help you more

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now