[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-02
Medium Priority
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

Habib Pourfard earned 1200 total points
ID: 38358958
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

ID: 38358995
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

872 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