One of the important parts of SQL Server development and optimization is the creation of indexes. In order to create proper indexing strategies it is necessary to understand how indexes work. This tutorial will guide you step by step to fully understand indexes.
There are only two different types of indexes. Clustered and NonClustered. There can only be one clustered index on a table and the reason is simple:
- A Clustered index is the data of table sorted according to the columns you choose.
- A NonClustered index is just like the index of a book. It contains data sorted so that it’s easy to find, then once found, it points back to the actual page that contains the data. (In other words, it points back to the clustered index)
Suppose we are reading a book about biographical information of all the U.S. Presidents, and the book itself orders the biographies starting from the first president to the latest president. This ordering would represent the clustered index.
Now suppose you asked two different people to find Franklin D. Roosevelt’s biography. One person was a historian and the other was an adolescent. The historian would quickly be able to find the presidents biography while the adolescent would have to scan through each page in order to find the biography. Even if the adolescent used the book’s index (akin to the non-clustered index), he would still have to search for the page after he found the page number.
So it is always faster to find information off of the clustered index because the data in already at the “leaf-level” off the index.
With this information, how do we determine what the clustered index should be? Well, it depends on the population of the people searching for the biographies. If it is mostly adolescents, then it would be more efficient to sort the book alphabetically rather than the historical order of the presidents.
Now let’s say that 75% of the population are historians and the other 25% are the adolescents. The historians typically need a lot of different information regarding the president’s biographies, while all the adolescents need is the president’s age at the time they took office. In this scenario, it is more plausible to keep the ordering of the book (clustered index) based on the order of the president, then simply add the age of the president in the back index of the book (the non clustered index). That way the adolescents do not have to look into the front of the book (clustered index) for the president’s age. This would satisfy both requirements and would be efficient for both historians and adolescents.
First let’s create our president’s table download and run: PresidentsTable
After running, let’s turn on the execution plan (In SQL Server Managment Studio place your mouse in the query window and select Query -> Include Actual Execution Plan)
Now execute the following query:
SELECT
PresidentNumber
,President
,YearsInOffice
,YearFirstInaugurated
FROM Presidents
WHERE PresidentNumber = 32
Now let’s view the execution plan:
Without a clustered index, our book is in no particular order. To find president 32, we need to scan every page.
Now let’s add a clustered index so we can organize our book according to PresidentNumber:
CREATE UNIQUE CLUSTERED INDEX IDX_C_Presidents_PresidentNumber ON Presidents(PresidentNumber)
And let’s run our query again:
SELECT
PresidentNumber
,President
,YearsInOffice
,YearFirstInaugurated
FROM Presidents
WHERE PresidentNumber = 32
Our execution plan now shows a “clustered index seek”. Meaning we did not have to look through every page of our book. We jumped right to page 32 and found the information on our president there.
In summary, when we look up information based on the clustered index (the way the table is physically sorted), we naturally find all the information we are looking for already there (President, YearsInOffice, YearFirstInaugurated).
Now, let’s create a non clustered index and look up the YearFirstInaugurated by president’s name:
CREATE NONCLUSTERED INDEX IDX_NC_Presidents_President ON Presidents(President)
Now let’s run our query to find the YearFirstInaugurated:
-- Force our query to use the index
-- (table is so small SQL Server bypasses it)
SELECT
YearFirstInaugurated
FROM Presidents WITH(INDEX(IDX_NC_Presidents_President))
WHERE President = 'Franklin Roosevelt'
If we look at our execution plan now, we will see that we initially looked the president’s name up in our index, then after finding the page where the presidents biography was located, we went to that page to grab the YearFirstInaugurated. This is denoted by the “Key Lookup”. (Also known as “Bookmark Lookup”)
This is a more expensive operation because our data is not at the “leaf-level” (or inline with the index we just searched), rather it is in the clustered index instead.
So how do we fix this? In SQL Server 2005, a new feature was introduced called “included columns”. This allows us to include data at the leaf-level of an index. So rather than looking up YearFirstInaugurated in the clustered index, we can find it in the nonclustered index. Let’s drop our index and include YearFirstInagurated in our nonclustered index:
DROP INDEX Presidents.IDX_NC_Presidents_President
GO
CREATE NONCLUSTERED INDEX IDX_NC_Presidents_President ON Presidents(President) INCLUDE(YearFirstInaugurated)
And run our query one more time:
-- Force our query to use the index
-- (table is so small SQL Server bypasses it)
SELECT
YearFirstInaugurated
FROM Presidents WITH(INDEX(IDX_NC_Presidents_President))
WHERE President = 'Franklin Roosevelt'
Now we only have an index seek. Because as soon as we looked the president up in the index, we immediately also found the YearFirstInaugurated:
VN:F [1.4.8_745]
Rating: 0.0/5 (0 votes cast)