Hi, everyone!
Today I'm going to talk about the differences and similarities between clustered indexes
and nonclustered indexes.
This is one of the most important things to understand when you're performance tuning
for a given workload.
In this video, though, I'm only going to talk about the structures because the performance
tuning aspects can cover a whole other video.
Just like the names of these objects are similar, so too are the internal implementations, but
don't be fooled by that subtlety.
The end results can have very different behaviour, and even place restrictions on what you can
do to the table.
Let's start by taking a look at what these structures are made of internally.
For my very simple example here, we'll use a table that models some of the data found
in a phone book: each row has a person's first name, last name, and phone number.
If the table has no indexes at all, this is called a heap, and when new values are inserted,
they're added wherever there's free space, and in no particular order.
Using this structure, if we were looking for a specific person's phone number, we would
have to check through every single row in the table, not only because there's no order
to the data, but also because even if we found a match, we wouldn't know if there were more
matches later on.
This type of searching is called a scan, and as you can see, it's very inefficient, particularly
in this case when we know there's a much better way.
Table scans aren't always a bad thing; it's just that if you don't need to touch all the
rows in a table, there's usually a better way to go about it.
In the phone book, the entries are sorted by last name, from A-Z, and if there are any
duplicates of last name, they're further sorted by first name, also from A-Z.
In database terminology, this is the index key, and it's an ordered list of columns and
their associated sort directions.
Going back to the example, there a couple different ways to structure the data to make
it more efficient to find a person's phone number.
Just like the real phone book, we can take the data in the table, and physically arrange
it into last name, first name order, but this is not enough by itself to solve the performance
problem.
It would end up doing a similar type of table scan as we saw before, but this time it would
know when to stop because the table data is ordered.
This is where the index part of things comes into play.
After the table data is put into physical order, SQL Server builds up a set of index
pages that allows queries to navigate directly to the data they're interested in.
This entire structure, including the base table data, is called a clustered index.
When a query navigates through the clustered index tree to the base table data, this is
called a clustered index seek.
This is very close to the same process you would use to find a person's phone number
from a physical phone book.
It doesn't only have the last name, first name, phone number entries, but it also has
some duplicated information that tells you about the range of last names on each page.
The index pages I mentioned work in a very similar way.
Since a clustered index contains the base table data itself, this is why you can only
create one clustered index.
It's impossible to physically arrange the same data in two different ways without having
a separate structure to store that information.
And this is where nonclustered indexes come in.
Let's start again with the heap structure.
The second way to give order to the data is to create a separate physical structure that
has the same index tree like the clustered index.
This time, though, instead of having the base data at the bottom -- or leaf -- level of
the tree, instead we have a set of pointers, or references, back to the base data.
With this type of structure, we can use any index key order we want because the order
is independent of the base table data order.
This entire structure is called a nonclustered index.
In this example, the base data is a heap, and the references back to it are RIDs -- or
row identifiers; these are the physical locations of the rows in the table.
Since a nonclustered index is separate from the base data, the base data could exist instead
as a clustered index.
In that case, the references back to it aren't RIDs, but instead are the clustered index
key values.
Just like a clustered index scan and seek, nonclustered indexes can also have those same
operations performed on them.
But the data directly available may be limited because usually nonclustered indexes only
include a subset of columns from the table.
If values from columns not in the index are requested, the query may navigate back to
the base data using the references I mentioned earlier.
If the query optimizer decides that doing that is too expensive, it may fall back on
scanning the base data instead of using the index.
This behaviour has very obvious performance implications.
Nonclustered indexes being separate from the base data brings about a couple of important
features.
Filtered indexes only contain rows that meet a user-defined predicate, and to create these,
you add a WHERE clause to the index definition.
A clustered index can't be filtered because it has to contain all the data in the table.
INCLUDE columns add copies of non-key column values to the leaf level of the index tree.
This means that queries using the nonclustered index won't have to incur the expense of navigating
back to the base data to get those non-key column values.
A clustered index, on the other hand, doesn't need INCLUDE columns, because all the columns
are available at the leaf level of the index.
Those two features only scratch the surface of the options there are when creating or
maintaining an index, so I've put some links below for further reading.
Some of the features and restrictions are version- and even edition-specific, so you'll
want to go through that material in quite a bit of detail.
The takeaway, as far as clustered vs. nonclustered goes, is that the clustered index is a way
of representing the base data as a whole.
A nonclustered index is a physically separate structure that references the base data and
it can have a different sort order.
As of making this video, the only other type of index in SQL Server that has a different
physical structure is a columnstore index.
This type of index was introduced as a nonclustered index in SQL Server 2012, and if the pre-release
documentation holds up, it will also be available as a clustered index in SQL Server 2014.
While this isn't directly related to the discussion about clustered vs. nonclustered indexes,
I want to take a short detour to talk about the two types of index-backed constraints:
primary key constraints and unique key constraints.
I've already written a full post about this on my blog, so I'll include a link below and
this will be brief.
Constraints in general are logical database objects.
The two types of constraints I mentioned create indexes behind the scenes, although SQL Server,
in theory, is free to implement them any way it likes.
By default, a primary key constraint creates a clustered index, while a unique key constraint
creates a nonclustered index.
They are that way partly for safety, in case the table designer doesn't realize that the
defaults can be changed, and partly because that's usually a pretty good starting point.
The main things to remember are that these constraints and the indexes that back them
are different constructs, and also that the type of indexes being created behind the scenes
can be changed.
For obvious reasons, this is definitely something you need to be aware of when performance tuning.
Again, if you want to go a bit more in-depth, you can check out the post on my blog.
So, I hope this clears up a lot of the confusion about clustered vs. nonclustered indexes.
Understanding the structures and how they work should give you a much better insight
into how to improve the performance of your system's workload.
If you found this video helpful, please let me know by clicking the Like button, and subscribe
to the channel to see more videos in the future.
Thanks for watching!