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.
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.
Saturday, April 18, 2015 - 7:54:43 PM - Tissa Rathnayake
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.
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
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.
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?
@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.
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.