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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
Former should read
form of

Big thumbs small keyboard
royjaydAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.