Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can a Table Really Have a Clustered Index?

By Hakim Ali,

The terms 'Clustered Index' and 'Non-Clustered Index' are not very intuitively worded for the concepts they represent. Besides being non-intuitive, they also imply a degree of similarity with each other. Perhaps this leads to a certain level of misunderstanding of these terms by not only those who are new to databases, but also by experienced data professionals who sometimes stumble while explaining them. Here is my attempt at providing a simple explanation of what they mean. I will also use this explanation to argue why a table cannot really "have" a clustered index.

Say you have a table that looks like this:

Employee_ID     Employee_Name
----------------------------------------------------------
1               DeeDee
5               Jennie
6               Jeb
2               David
8               Jose
3               Chris
7               Tom
4               Ed

Let's assume there is no clustered index on this table. This makes it a heap, meaning the rows are not physically written to disk in any particular order. When you search for a record in such a table via a select statement, the database must look at each record in the table. This is called a table scan. Not a very good way of doing things, because it is bad for performance.

Imagine looking through an unordered phone book for a specific name. You would have to read every name in the book to find your result. You would not stop at the first instance of that name because you could not be sure that you have found all instances of that name. Not very efficient.

Adding a clustered index to the table physically re-orders the records on disk. Let's say we add a clustered index on the Employee_ID field (there already are many articles on which fields to pick as clustered indexes, so I won't revisit that here). This would cause the rows to be ordered thus on disk:

Employee_ID     Employee_Name
----------------------------------------------------------
1               DeeDee
2               David
3               Chris
4               Ed
5               Jennie
6               Jeb
7               Tom
8               Jose

By the way, a primary key is a clustered index by default. It doesn't necessarily have to be clustered, but it is by default.

Back to our example, our table is no longer a heap. The records are ordered by Employee_ID. Now if you were searching for an employee with a specific ID, such as this

select *
 from Employee
 where Employee_ID = x

the database would no longer have to scan every record in the table. It would most likely "seek" the record using the clustered index and find the record much faster. However, if you wanted to find records by name

select *
 from Employee
 where Employee_Name = 'x'

you would still be causing a scan because the table is ordered by Employee_ID, not Employee_Name. This is like having a phone directory with all names ordered by some arbitrary ID number and not by the alphabet. This is excellent if you are looking for a friend whose ID number you know. It is not very good if you only have the name.

This is where a nonclustered index comes in. I like to think of a nonclustered index as an ordered copy of one or more fields. Doesn't "ordered copy of fields" sound much more intuitive than "nonclustered index"? Adding a nonclustered index to our table on the field Employee_Name would cause a second structure (an ordered copy of Employee_Name) to be created on disk, and it would look something like this:

IDX_Employee_Name
----------------------------------------------------------
Chris       Pointer to 3
David       Pointer to 2
DeeDee      Pointer to 1
Ed          Pointer to 4
Jeb         Pointer to 6
Jennie      Pointer to 5
Jose        Pointer to 8
Tom         Pointer to 7

Note that the index has records ordered by Employee_Name. Now, when you look for an employee by name, the database engine no longer scans the entire table (or the clustered index). It quickly seeks the name you are looking for in the nonclustered index, which is easy to do because this structure is ordered by name. You don't even have to read all the records to know you have found all instances of a specific name, because they are all located together in one place by virtue of being ordered.

However, there's more work to be done. Once it has found the name you want, the database still needs to find the full record in the table. For this, it uses the pointer, which is simply a pointer to the location of the matching record in the clustered index. The database does something like this:

  1. Find the name in the nonclustered index.
  2. Follow the pointer to the full record in the clustered index (this, by the way, is called a bookmark lookup).
  3. Return the record from the clustered index.

Note that steps 2 and 3 only happen if your query is looking for fields that don't already exist in the nonclustered index. If all query fields (filters and return values) exist in the nonclustered index, they are "covered" and a bookmark lookup is not required at all. In this situation, the data is simply returned from the nonclustered index.

Going back to definitions, the terms "Clustered Index" and "Nonclustered Index" sound very similar and imply they are similar things. However, besides the fact that they help make searching easier, they don't have very much in common. A nonclustered index is simply an ordered copy of one or more fields in the table, with pointers back to the table. Because it is a copy of table fields, you can have many nonclustered indexes per table. A clustered index causes reordering of the physical records of the table on disk. This is because you can only write the records on disk once for the entire table. You can only order the records one way (any way you choose, but only one way at any given time). The entire contents of the table, all fields, all rows, all its data, become the clustered index. Therefore, there can only be one clustered index per table. Because the clustered index is the entire table, a table cannot so much "have" a clustered index as it "is" a clustered index (unless it's a heap). To say that a table has a clustered index is like saying a phone book has a listing of names and numbers. It does not have a listing, it is the listing.

Total article views: 8183 | Views in the last 30 days: 5
 
Related Articles
FORUM

clustered index

clustered index

FORUM

Insert on a clustered index - is column ordering (ASC or DESC) important ?

insert clustered index column order asc desc

FORUM

Clustered Index

SQL Server(clustered Index)

FORUM

cluster index or non cluster index

cluster index or non cluster index

BLOG

Clustered index vs Non clustered index

Clustered index vs Non clustered index structure Clustered and Non clustered indexes are stored in...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones