clustered index v/s non clustered index (in laymans terms)

Dear experts
I would like to know the difference between index types (with focus on clustered index and non-clustered)index in laymans terms.

We use sql server 2008 and in the Microsoft SQL server management studio when i want to create index i do see following options :

Primary XML

Any idea what to use when? An explanation in layman's terms will be appreciated.
Who is Participating?
Scott PletcherSenior DBACommented:
The clustered index is the physical order of how the table is stored; in fact, in SQL Server, the clustered index is the table itself.  For example, for a phone book, the clustering index would be keyed on ( LastName, FirstName, MiddleName ).  A table can have only one clustered index.  The clustered index contains every column in the table.

A nonclustered index contains only the columns it requires.  These include all key columns, the clustering key column(s), and any columns you "told" SQL to include in that index.

For example, we could create a nonclustered index on the phone book data keyed by phone_number (to speed lookups of a specific number).  That index would then contain these columns:
    phone_number [key column for the index];
    first_name, last_name and middle_name [clustering key columns].  
No other columns would appear in that index.

The first index created on a table should always be a clustered index.  In general, cluster the table on how it is most often or always looked up.  Do NOT default to an identity clustering key.  Instead, review the table's actual usage and try to determine the best clustering key(s) for the table.  Creating the best clustered index is the most critical aspect for good performance on the table.
The layman's example that I use to explain the difference between clustered and non-clustered indexes is this.  Clustered indexes are like a dictionary.  When you look up a word, you're already at the data.  You don't have to go anywhere else.  Non-clustered indexes are like indexes at the back of a book.  You look up a term and the index points to a location in the book where the data is.

Spatial indexes are special purpose. If you store and use spatial data (eg. Delivery company calculating routes) then you use spatial indexes.

Primary xml relates to storing data in xml
So again this is specific to a former of data.
Documentation  for it
Former should read
form of

Big thumbs small keyboard
royjaydAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.