Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Understanding SQL Server Indexing

MSSQLTips author Greg Robidoux By:   |   Read Comments (18)   |   Related Tips: 1 | 2 | 3 | More > Indexing

Problem
With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.

Solution
From a simple standpoint SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data.  A table can only have one Clustered index and up to 249 Non-Clustered Indexes.  If a table does not have a clustered index it is referred to as a Heap.  So what does this actually mean?

To further clarify this lets take a look at what indexes do and why they are important. The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. If you have ever looked at a query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.

Here are some examples of queries that were run.  These were run against table dbo.contact that has about 20,000 rows of data.  Each of these queries was run with no index as well as with a clustered and non-clustered indexes.  To show the impact a graphical query plan has been provided. This can be created by highlighting the query and pressing Control-L (Ctrl-L) in the query window.

1 - Table with no indexes
When the query runs, since there are no indexes, SQL Server does a Table Scan against the table to look through every row to determine if any of the records have a lastname of "Adams".    This query has an Estimated Subtree Cost of 0.437103. This is the cost to SQL Server to execute the query. The lower the number the less resource intensive for SQL Server.

 

2- Table with non-clustered index on lastname column
When this query runs, SQL Server uses the index to do an Index Seek and then it needs to do a RID Lookup to get the actual data. You can see from the Estimated Subtree Cost of 0.263888 that this is faster then the above query.

 

3- Table with clustered index on lastname column
When this query runs, SQL Server does an Index Seek and since the index points to the actual data pages, the Estimated Subtree Cost is only 0.0044572.  This is by far the fastest access method for this type of query.

 

4- Table with non-clustered index on lastname column
In this query we are only requesting column lastname.  Since this query can be handled by just the non-clustered index (covering query), SQL Server does not need to access the actual data pages.  Based on this query the Estimated Subtree Cost is only 0.0033766.  As you can see this even better then example #3.

To take this a step further, the below output is based on having a clustered index on lastname and no non-clustered index. You can see that the subtree cost is still the same as returning all of the columns even though we are only selecting one column.  So the non-clustered index performs better.

5- Table with clustered index on contactId and non-clustered on lastname column
For this query we now have two indexes.  A clustered and non-clustered. The query that is run in the same as example 2. From this output you can see that the RID Lookup has been replaced with a Clustered Index Seek. Overall it is the same type of operations, except using the Clustered Index.  The subtree cost is 0.264017.  This is a little better then example 2.

So based on these examples you can see the benefits of using indexes.  This example table only had 20,000 rows of data, so this is quite small compared to most database tables.  You can see the impact this would have on very large tables.  The first idea that would come to mind is to use all clustered indexes, but because this is where the actual data is stored a table can only have one clustered index.  The second thought may be to index every column. Although this maybe helpful when querying the data, there is also the overhead of maintaining all of these indexes every time you do an INSERT, UPDATE or DELETE.

Another thing you can see from these examples is ability to use non-clustered covering indexes where the index satisfies the entire result set.  This is also faster then having to go to the data pages of the Heap or Clustered Index.

To really understand what indexes your tables need you need to monitor the access using a trace and then analyze the data manually or by running the Index Tuning Wizard (SQL 2000) or the Database Engine Tuning Advisor (SQL 2005). From here you can tell whether your tables are over indexed or under indexed.

Next Steps



Last Update: 3/22/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, May 18, 2012 - 8:32:36 AM - Har Read The Tip

SQL Server has two basics kinds of indexes. They are clustered and nonclustered indexes. There are some fundamental differences to the two which are key to understanding before you can master index tuning.

 

First the things that they have in common.

Both clustered and nonclustered indexes can be made up of more than one column. The columns are put in the index in the order you specify them in the CREATE INDEX statement (or the order they are shown in the UI). They are also sorted in this order as well. Indexes are first sorted on the first column in the index, then any duplicates of the first column and sorted by the second column, etc. You can have up to 16 columns specified as indexed columns.

Neither clustered or nonclustered indexes will guarantee the sort order of the data when it is being returned to you. If the order of the data matters to you, you should always sort the data with the ORDER BY clause in your select statement.

Both clustered indexes, and nonclustered indexes take up additional disk space. The amount of space that they require will depend on the columns in the index, and the number of rows in the table. The clustered index will also grow as you add columns to the table (keep reading, it’ll make sense later on).

Adding indexes (both clustered and nonclusterd) will increase the amount of time that your INSERT, UPDATE and DELETE statement take, as the data has to be updated in the table as well as in each index. If you have filtered indexes in SQL Server 2008 and the records you are updating are not included in all your indexes, SQL Server should only have to update the values in the indexes which the records are stored within.

Columns of the TEXT, NTEXT and IMAGE data types can not be indexed using normal indexes. Columns of these data types can only be indexed with Full Text indexes.

If you wish to rebuild your indexes online (without locking the table) and have Enterprise edition do not index TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) data types as including columns with these data types will require that you rebuild the index offline.

The total size of the key columns can not exceed 900 bytes.  Don’t forget that uni-code characters take up two bytes per character which will reduce the number of characters which your index can hold.


Wednesday, February 27, 2013 - 6:14:29 PM - Harikrishna Read The Tip

Nice Artical


Tuesday, March 19, 2013 - 1:00:32 PM - RickNPHX Read The Tip

Once an index is created, can it later on be deleted? Once an index has been defined, can the attributes of clustered or non-clustered be changed?

thanks,

Richard


Wednesday, March 20, 2013 - 10:27:25 AM - Greg Robidoux Read The Tip

@RickNPHX

yes once you create an index you can later DROP it using the DROP INDEX command.

http://msdn.microsoft.com/en-us/library/ms176118.aspx

You can use the CREATE INDEX... WITH DROP EXISTING to change the structure of an index.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

 


Friday, April 12, 2013 - 1:49:55 AM - steven Read The Tip

Great information! Thanks.


Monday, May 06, 2013 - 2:54:20 AM - srinivasan.d Read The Tip

While using more than 20 join in a query. How does index increase the performance in sql server 2008. Could you explain me?


Monday, May 06, 2013 - 8:25:59 AM - Greg Robidoux Read The Tip

@srinivasan.d - indexes help SQL Server find the data quicker without having to search all of the data in a table.   When you join tables you are usually joining on indexed columns so this allows SQL to find the necessary data faster so the query will complete quicker.  As you add more tables to your join each table that is pulled in will use an index for that table if one exists therefore reduincg the overall time for the query to complete versus having to scan through all of the data to find the necessary data.


Monday, May 06, 2013 - 9:42:14 AM - srinivasan Read The Tip

Thanks Greg,  It's Very useful.


Monday, December 16, 2013 - 2:44:04 PM - Heloiza Read The Tip

Hi.

Sorry, I didn´t understand why you say, on item 5,  "...This is a little better then example 2...". On the "2- Table with non-clustered index on lastname column" the estimated subtree cost is 0.263888 but, on item 5, the estimated subtree cost is 0.264017 (greater)... could you explaim me this, please?

Thanks.


Monday, December 16, 2013 - 3:47:07 PM - Greg Robidoux Read The Tip

Hi Heloiza, I haven't looked at this tip in quite some time and you are right the cost for #5 is a little more than for #2, but overall these are pretty similar and the overall cost is very low for both options.

For #5, since we have a clustered index we are doing an Index Seek and for #2 we have no clustered index so we are doing a heap lookup. 

Thanks for bringing this to my attention.  I will review the tip again and make updates where needed.


Wednesday, December 18, 2013 - 9:45:57 AM - Heloiza Read The Tip

Hi, Greg. Thanks for your reply. 

Your article/tip is really very interesting and useful. Because of this, I suggested it to my work team...

Thanks again.

 


Friday, January 03, 2014 - 12:12:37 PM - syed jilani basha Read The Tip

nice article and easy to understand


Sunday, January 19, 2014 - 1:31:34 PM - Vidhi Rathod Read The Tip

 

Really nice article !

Thanks Greg.


Monday, March 24, 2014 - 6:00:05 PM - sunil Read The Tip

Hi

Its really usefull


Monday, March 24, 2014 - 6:09:22 PM - sunil Read The Tip

Hi Greg

if there is Emp table with  EMPNO(Primary Key)  column and then automatically  it creates CLUSTERED KEY . Will it create another Clustered table and sort the data or it will direclty Sort the data in EMP table.Please clarify my dought

 

Thanks 

sunil


Tuesday, March 25, 2014 - 9:17:58 AM - Greg Robidoux Read The Tip

Sunil,

If you have a table without a clustered index and then add one SQL Server will sort the data in temporary objects and the final result is that you have one table at the end that has the clustered index.

 

 


Wednesday, May 07, 2014 - 8:08:40 PM - konard Read The Tip

Hi Greg

I don't understand points

2- Table with non-clustered index on lastname column

and
4- Table with non-clustered index on lastname column


the title sugests that this is the same scenario with completely different results


Thursday, May 08, 2014 - 9:55:27 AM - Greg Robidoux Read The Tip

Hi Konard,

The first query does a "SELECT * ..." and the second query does a "SELECT lastname ...", so it shows how the differences in behavior based on the columns that you are selecting.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.