Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

Clustered Index vs. Non-Clustered Index in SQL Server

Those new to SQL Server performance tuning or database indexes in general are probably confused by the differences between clustered and non-clustered indexes. Usually, there is a performance problem that prompts research and you finally have to "bite the bullet" and look into what the hell these index types are.

What is a Database Index?

First, it is important to understand what an index is and why you need to know about them. The simplest way to think about indexes is that they are "copies" of your data in a table that is sorted a certain way. For instance, if you had a table that looked something like this:



sample table schema to do indexes on

Let's say this table has 100,000's of rows and you need to query by LastName in most of your queries. By default, when you create this table, your data will be stored on disk and sorted by the "Id" primary key column. This default sort is called the "Clustered Index". Queries for LastName will be slower though because the data is not sorted by LastName so a SELECT statement will have to do a "full table scan" to find the record you are looking for. You could really speed up these queries that search by LastName if you were to have the data sorted by LastName instead of by Primary Key.

Another way to speed up your LastName queries is to add a "secondary index" called a "Non-clustered" index. This will make a copy of your table data and store it on disk but will have it sorted by LastName instead of primary key. Now, when you do a select where LastName = "something", SQL Server's query optimizer is smart enough to know to use your "copy" of your table to search for LastName because it is sorted by that column instead of just table scanning.

Clustered Indexes

There is only 1 clustered index allowed per table so choose wisely. This index should be the most common column that is in your WHERE clauses for queries against this table. So if most of the time you search by primary key, then leave it as the default. But if you search by DateCreated or LastName most of the time on this table, then you might want to consider changing the clustered index to this column instead.

Some things to remember when using clustered indexes:

  • The reordering of the index occurs every time the index changes (ie: on Updates, Inserts, Deletes).
  • Affects the physical order of data so there can only one clustered index.
  • Keeps the rows in order within a page (8k) of data. The pages are not physically ordered except after an ordered load or re-index based on that cluster.
  • Re-orders the way records in the table are physically stored.
  • Choose this index wisely as there can only be one. Rule of thumb: Apply to a unique, somewhat ordered, and commonly queried column.
  • Like the pages of content in a book. Each page is a collection of data. The order (page numbers 1, 2, 3, etc.) that the data is stored in is controlled by the clustered index.

There a few things to keep in mind when changing the default clustered index in a table:

  1. Lookups from non-clustered indexes must look up the query pointer in the clustered index to get the pointer to the actual data records instead of going directly to the data on disk (usually this performance hit is negligble).
  2. Inserts will be slower because the insert must be added in the exact right place in the clustered index. (NOTE: This does not re-order the data pages. It just inserts the record in the correct order in the page that it corresponds to. Data pages are stored as doubly-linked lists so each page is pointed to by the previous and next. Therefore, it is not important to reorder the pages, just their pointers and that is only in the case where the newly inserted row causes a new data page to be created.)

Best Practices for Clustered Indexes

  • Large amount of selects on a table, create a clustered index on the primary key of the table. Then create non-clustered indexes for all other columns used in selects and searches. Put non-clustered indexes on foreign key/primary key columns that are used in joins.

Non-clustered Indexes

Non-clustered indexes are not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index. This is why the clustered index you choose is so important because if effects all other indexes.

There are 2 modes for non-clustered indexes, Non-unique and unique. Non-Unique means that the index does not act as a constraint on the table and does not prevent identical rows from being inserted. Unique constraints mean that the index prevents any identical rows from being inserted.

  • Does not re-order the actual table data.
  • Sometimes called a "heap table" for tables lacking clustered indexes because it points to the actual data pages that are essentially unordered and non-indexed.
  • If no clustered index, non-clustered indexes point to the actual data in the table.
  • If clustered index present, non-clustered index point to clustered index.
  • Logical order of the index does not match the physical stored order of the rows on disk.
  • Similar to an index in the back of a book. The actual data is stored in the pages of the book but the index reorders and stores a pointer to each data value.

Best Practices for Non-clustered Indexes

  • Add non-clustered indexes for queries that return smaller result sets. Large results will have to read more table pages anyway so they will not benefit as much from a non-clustered index.
  • Add to columns used in WHERE clauses that return exact matches.
  • If a clustered index is not used on these columns, add an index for collections of distinct values that are commonly queried such as a first and last name column group.
  • Add for all columns grouped together for a given query that is expensive or very common on a large data table.
  • Add to foreign-key columns where joins are common that are not covered by the clustered index.

Indexes are a lot of "trial and error" depending on your database design, SQL queries, and database size.

Index type for Primary key/Foriegn Key

"Put non-clustered indexes on foreign key/primary key columns that are used in joins."

I am confused. Should we create Clustered or Non-Clustered indexes on Primary Key/Foreign Key columns?

Non-Clustered Index

When we create Non-Clustered Index then this will make a copy of your table data and store it on disk.
My question is, when it stored on disk it is permanently or temporary.

Regards,
Mahesh Dhiman

Great explanation and more questions

So a clustered index should be placed on the column that you are most likely to search on, the more unique the better? I notice that if I add a clustered index on a column other than my primary key, my select statements return them ordered by that column instead of the primary key.

A non-clustered index, however, creates a sorted "copy" of the table that points to the data page that the record is located on?

Hopefully I understand this correctly.

Is there ever a need to have a non-clustered index without a clustered index?

Thanks for the great post!

yeah it's nice

yeah it's nice

clustered Indexes

Pls give some sample query and table

Seems to be contradictory?

Another way to speed up your LastName queries is to add a "secondary index" called a "Non-clustered" index. This will make a copy of your table data and store it on disk but will have it sorted by LastName instead of primary key.
...
Non-clustered indexes are not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index.

"Copy" in this case is a

"Copy" in this case is a figure of speech. It is actually a list of "pointers" to the data but you can think of it as a copy to understand it easier.

your explanation was

your explanation was good.thank u..

Congratulations. Perfect

Congratulations. Perfect explanation

I have spent the past few

I have spent the past few days reading articles about clustered/non-clustered indexes and it's mostly been flying over my head...this article explained it perfectly! Really well written! Thanks!

simply superb

best explanation for me as far .......

awesome article masssthhh

awesome article masssthhh explanation...

Very Delicious article. I

Very Delicious article. I really enjoyed and got what ever I needed..
thanks for the effort.

Very Clear and Crisp

Very Clear and Crisp

Good Explain

i need this explain with example

index

example for nonclustered index

Thank You

Thank You so much. It really helped me.

Nice Explanation

Very well explained and also helpful.

thanks a lot ..!

THANK YOU

Thank you for this post! It really helped me understand the differences between clustered and non-clustered indexes. I have never had to do database tuning but now I have a better understanding for future reference. It will also help in interview questions but even better for real world practice.

Thank you for the real world examples of when and how to use the indexes. VERY HELPFUL!!!

A very nice article

Thank you
i read ur article, it is best article and helpful me for a interview.

Mr. Amol Prakash Mandavkar

Comment about Post

Hi,

I just need to confirm whether .
there is only one clustered index and many non-clustered indexes.
but how many non-Clusterred indexes should present?

and why actually need to know about them in detail???

nice article....

Clearly explained. Thnks.

Good

Good

Nice Explanation

Article is so nice. The person who does not know any thing about indexes can understand very easily by reading this article. Good thing is explanation wiht general and simple example. Thanks a lot....

Index

Normally I do not leave any comment as I am perhaps lazzy or even selfish...but in this instance, I was compelled to make the following statement:

"Fantastic article for anyone who wants to know what clustered & non-clustered indexes / indeces are?"

cheers mate...I owe you a pint

Ahsanul
UK

SQL Secondary Indices

A thorough description of the pros and cons of indexing. A lot of apps I've worked on simply use a system generated integer primary key when an existing business specific key could have been used. As a result all queries run slower and unnecessary secondary indices have to be created to speed up access. I would advise that a business specific key (i.e staff number)sghould be used at every copportunity.
thanks .

Good one!

Keep doing the good work.
Thanks
Kuldeep Rana

Very Nicely Done/

Thank you.

Above article

very good

clusters

Thank you!

nice one!! really

nice one!! really informative.

Like

Simple and infomative

Good

really really good one, it saved my much more time.....thanks

Question

" Another way to speed up your LastName queries is to add a "secondary index" called a "Non-clustered" index. This will make a copy of your table data and store it on disk but will have it sorted by LastName instead of primary key. "

but,

" Non-clustered indexes are not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index. "

Does Non-clustered index create copy of the table?

Saying "copy" means "pointers"

Generally, in the article, when I say "copy of the table", that is meant to be conceptual because none of the data in non-clustered indexes is copied but instead is actually a "jump table" of pointers to the physical rows.

great article

Thank you for explaining this in simple layman's terms.

This information is very much

This information is very much appretiated..Please try that next time when explaining all this, use the syntax for elaboration on how indexes works..this is so because for some people new to the concept, for them it does not make no sence..Thank You..

like

like

copy means pointer

copy means pointer

ghd iv salon styler

thanks for share!
ghd iv salon styler http://ghdsiv.org

for beginners who are oblivious to clustered and non-clustered

Well explained in simple words. Really helpful for beginners new to clustered and non-clustered index world. Probably a couple of links to continue to next advanced level on these topics could make it more useful.

Nice one

nothing can stop me adding this article to my bookmarks

Excellent

Excellent

marvelous

Iam reading a ms certification book.Iam kind of confused about indexes,but after reading this article iam lot more comfortable with indexes.thank you.

awe some explanation

awe some explanation

Nice Article

Thanks for clearing the picture of indexes.

Thank you, this topic is very

Thank you,
this topic is very helpful

Good Article

Thank you sir
i think that it is most popular topic in sql server,you doing very well sir.
Regards,
Shaikh

add with query

add with query