Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Understanding SQL Server Indexing


By:   |   Read Comments (25)   |   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 999 Non-Clustered Indexes (depending on SQL version). 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, 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 almost the same as 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 probably imagine 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. From here you can tell whether your tables are over indexed or under indexed.

Next Steps


Last Update:





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





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, June 03, 2016 - 1:37:06 AM - Ashish Badame Back To Top

 Hi,

     it's a great site which gives very detailed knowledge of MS SQL.


Sunday, April 19, 2015 - 6:57:42 AM - Greg Robidoux Back To Top

Thanks Tissa.  It looks like this changed in a later release of SQL Server.  In SQL Server 2005 it was 249, but it looks like this was changed in SQL Server 2008 to 999 non-clustered indexes.  Thanks for pointing that out.

 

-Greg


Saturday, April 18, 2015 - 7:54:43 PM - Tissa Rathnayake Back To Top

Hi Greg,

I have seen on books that there can be upto 999 Non-Clusterd Indexes and not 249. and each index can have upto 16 columns and the size of the index cannot exceed more than 900 Bytes.

 

Thanks

Tissa


Tuesday, February 10, 2015 - 7:59:56 AM - starbina Back To Top

thanks a lot


Thursday, November 27, 2014 - 6:33:03 AM - RAVINDER Back To Top

we want first record sort then record update, for exp.

 

Table Colnums:

AC_NO    PART_NO      SERIAL_NO

 

Frist Ac_no second PART_NO third SERIAL_NO sort then after recoed update contiune serial start from 1


UPDATE accounts2 SET id = NULL
GO 

DECLARE 
@id INT 
SET 
@id 
UPDATE accounts2 
SET @id id @id 10
GO 

 

 

Above record firstly sort then updated.

 

Please help me.

regards,

E-mail romana.ravinder@gmail.com


Wednesday, October 08, 2014 - 12:24:56 PM - Greg Robidoux Back To Top

Hi Chantal, #3 is using a CLUSTERED INDEX and #4 is using a NONCLUSTERED INDEX.  The SELECT query is only pulling back one column for both examples.

-Greg


Wednesday, October 08, 2014 - 11:27:35 AM - Chantal Gonzales Back To Top

Your transition from numbers 3 to 4 is very vague. 


Thursday, May 08, 2014 - 9:55:27 AM - Greg Robidoux Back To Top

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.


Wednesday, May 07, 2014 - 8:08:40 PM - konard Back To Top

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


Tuesday, March 25, 2014 - 9:17:58 AM - Greg Robidoux Back To Top

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.

 

 


Monday, March 24, 2014 - 6:09:22 PM - sunil Back To Top

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


Monday, March 24, 2014 - 6:00:05 PM - sunil Back To Top

Hi

Its really usefull


Sunday, January 19, 2014 - 1:31:34 PM - Vidhi Rathod Back To Top

 

Really nice article !

Thanks Greg.


Friday, January 03, 2014 - 12:12:37 PM - syed jilani basha Back To Top

nice article and easy to understand


Wednesday, December 18, 2013 - 9:45:57 AM - Heloiza Back To Top

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.

 


Monday, December 16, 2013 - 3:47:07 PM - Greg Robidoux Back To Top

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.


Monday, December 16, 2013 - 2:44:04 PM - Heloiza Back To Top

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, May 06, 2013 - 9:42:14 AM - srinivasan Back To Top

Thanks Greg,  It's Very useful.


Monday, May 06, 2013 - 8:25:59 AM - Greg Robidoux Back To Top

@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 - 2:54:20 AM - srinivasan.d Back To Top

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


Friday, April 12, 2013 - 1:49:55 AM - steven Back To Top

Great information! Thanks.


Wednesday, March 20, 2013 - 10:27:25 AM - Greg Robidoux Back To Top

@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

 


Tuesday, March 19, 2013 - 1:00:32 PM - RickNPHX Back To Top

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, February 27, 2013 - 6:14:29 PM - Harikrishna Back To Top

Nice Artical


Friday, May 18, 2012 - 8:32:36 AM - Har Back To Top

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.


Learn more about SQL Server tools