Difference between clustered and nonclustered index

sql-server database-design indexing clustered-index non-clustered-index
Pabuc picture Pabuc · Feb 21, 2011 · Viewed 280.8k times · Source

I need to add proper index to my tables and need some help.

I'm confused and need to clarify a few points:

  • Should I use index for non-int columns? Why/why not

  • I've read a lot about clustered and non-clustered index yet I still can't decide when to use one over the other. A good example would help me and a lot of other developers.

I know that I shouldn't use indexes for columns or tables that are often updated. What else should I be careful about and how can I know that it is all good before going to test phase?

Answer

riandp picture riandp · Aug 21, 2011

A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book.

A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.

Related questions

Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?

Comments to question "How to decrease response time of a simple select query?" tell: "What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG …

Read More
What columns generally make good indexes?

As a follow up to "What are indexes and how can I use them to optimise queries in my database?" where I am attempting to learn about indexes, what columns are good index candidates? Specifically for an MS SQL database? …

Read More
How to create materialized views in SQL Server?

I am going to design a DW and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with an query example..

Read More