Clustered and Non Clustered Indexes

[cpitc]

Could someone explain in simple terms the difference between a clustered and a non clustered Index, and why you would use one or the other. What advantages do they have against each other.

Thanks

 

[djj55]

There can be only on clustered index on a table. In simple terms clustered means the index uses the data where a non-clustered index uses pointers to the data.

Notice this is a simple explanation as requested so I hope the smarter people do not burn me.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Like djj said, you can only have one clustered index on each table. This is because the data in the table is physically sorted to match the clustered index.

Under normal circumstances, clustered indexes give better performance than non-clustered indexes. This is because the data in the table is located in the index.

non-clustered index uses pointers to the data

This is kinda true, and also kinda misleading. Let me explain. In a non-clustered index, there is, indeed, a pointer to the remainder of the data. But the data in the index is also included in the index. Make sense? I didn't think so.

Image you have a table of cars at a car dealership. Every car has a unique identifier (VIN). So, this could be your primary key and would also be a good candidate for a clustered index because you will likely have other tables to track other things (like oil changes and new tires). When you write a query that filters on VIN, a clustered index seek is done to VERY quickly locate the row where the data is stored. All the data in the row is now accessible (like color, make, model, etc...). Since the VIN is used in other tables (foreign key relationships), it is best to use it in your clustered index.

However, you will probably want to have other indexes on this table too. For example, you may want to have an index Color. Someone may walk in and say, I want a blue car. so....

Select * From Cars Where Color = 'Blue'

In this case, the color index should be non-unique, non-clustered. This index will contain one row for each row in the actual table. There will be a pointer for location of the data, and there will also be the Color stored in the index.

We're finally getting to the point I wanted to make. In the previous example, (the color index), the color is stored in the index. Now, because of this, SQL can occasionally take a "shortcut" depending on the query. If you write a query against the cars table that ONLY uses the color column, SQL can get all of the data from the index without actually having to look at the table itself. For example:

[tt]Select Color, Count(*) As ColorCount
From Cars
Group By Color[/tt]

The only column used from the cars table is the color column. There's no reason for SQL to actually look at the table data, so it doesn't. You may occasionally hear the term "Covering Index". An index is considered to be a covering index when SQL can use the data in the index (for a query) without having to go to the table for it.

I wrote a blog that helps to explain covering indexes:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-covering-indexes

why you would use one or the other

SQL Server, by default, creates a clustered index on your primary key. 99.9% of the time, this is what you want. I personally think that every table should have a primary key. Most of the time, the primary key should be clustered. In fact, my DB has a couple hundred tables and EVERY one of them has a primary key and that primary key is clustered.

What advantages do they have against each other.

Clustered indexes are usually faster than non-clustered indexes. The only exception to this is when a query can use a non-clustered (covering) index instead of the actual table data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cpitc]

ok thanks for the answers, so if I am understanding correct you can have only have one clustered index in a table and the rest of the indexes must be non clustered.

Or if im wrong why would you use a non clustered index if a clustered is better?

Thanks

 

[gmmastros]

ok thanks for the answers, so if I am understanding correct you can have only have one clustered index in a table and the rest of the indexes must be non clustered.

Yes. Exactly.

Or if im wrong why would you use a non clustered index if a clustered is better?

Clustered indexes are better than non-clustered indexes. But, depending on your query, it may be better to use a non-clustered index.

Ex:
[tt]
ID Color
-- -----
1 Red
2 Blue
3 Green
4 Purple
5 Yellow
6 Gray
7 Black
8 White
9 Orange
[/tt]

Take a look at the list shown above. If I asked you to tell me the color for ID = 4, you would quickly jump to the spot in the list and tell me Purple. This would be the same as....

Select Color From TableName Where ID = 4

If there was a clustered index on the ID column, SQL would be able to use that index to quickly locate the row you want and return the data.

Now, suppose I asked you to tell me the ID number for the color White.

Select ID From TableName Where Color = 'White'

Since the color column is not sorted, you are forced to start at the for row and scan your way down through the list until you find the one you want. The clustered index on the ID column will not help for this query. So... you could add a second index to the table. Since you have a clustered index already, this must be a non-clustered index. A non-clustered index on the color column would allow searches on that column to be faster.

As you can see. There can be multiple indexes on a table, but only one of them can be clustered.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cpitc]

Ok thanks that makes perfect sense now. Thanks for your help and good explanations.

 

[gmmastros]

You are welcome.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom