Clustered Index
Only 1 allowed per table
Physically rearranges the data in the table to conform to the index constraints
For use on columns that are frequently searched for ranges of data
For use on columns with low selectivity
Non-Clustered Index
Up to 249 allowed per table
Creates a separate list of key values with pointers to the location of the data in the data pages
For use on columns that are searched for single values
For use on columns with high selectivity
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows
Included Column Index (New in SQL Server 2005)
In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Another advantage is that using non-key column in index we can have index data types not allowed as index key columns generally.
In following example column FileName is varchar(400), which will increase the size of the index key bigger than it is allowed. If we still want to include in our cover index to gain performance we can do it by using the Keyword INCLUDE.
USE AdventureWorks GO CREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName)
Non-key columns can be included only in non-clustered indexes. Columns can’t be defined in both the key column and the INCLUDE list. Column names can’t be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. For Included Column Index to exist there must be at least one key column defined with a maximum of 16 key columns and 1023 included columns.
Avoid adding unnecessary columns. Adding too many index columns, key or non-key as they will affect negatively on performance. Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency. More disk space will be required to store the index. Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.
Another example to test:
Create following Index on Database AdventureWorks in SQL SERVER 2005
USE AdventureWorks GO CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID) GO
Test the performance of following query before and after creating Index. The performance improvement is significant.
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN '98000' AND '99999'; GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
How do you determine which column are already included in an index
if you run sp_help {tablename}
or sp_helpindex {tablename}
is only tells you what columns make up the index, not which columns are also included
hi!!
can you explain the difference between Key column and fields. Because as an advantage of included index you said the 16 key column and index size 900 bytes. But you also said the we can include 1023 fields.. I am confused.
Hi Pinal,
We have a SQL Server 2008 R2 databases and don’t use a PK & hence no CLUSTERED Index on the table.
We have only Non Clustered Indexes as part of our design.
So just wanted to know whether there is an alternative to Reorganize of Indexes for the HEAP Structures.
If Yes then how this needs to be done?
@Jeetendra.
Primary Key by default creates a Clustered Index, but it is not mandatory to link both of them.
It means, you can have Clustered Index on a non Primary Key Column as well. If you create Clustered Index on a Non-Primary Key column, which has duplicates, SQL Server can still Creates Clustered Index on that column by making it unique internally. It adds Unique Numbers to the Non- Primary Key Column which makes that column Unique and then creates an Index on it, because this is internal to SQl Server, we cannot see it in SSMS.
Be very sure, to test out before deploying this to production. Choose wisely which column(s) to pick up for Clustered Index.
~ IM.
How can i alter Clustered Index …..?
create CLUSTERED index inddex_as
on Authors(phone)
I want to Alter As
alter CLUSTERED index inddex_as
on Authors(phone,author_id)
Please Help me…
Thanks …
Hi Pinal,
You mention that Clustered Index “Physically rearranges the data in the table to conform to the index constraints”.
However, as per Brad McGehee’s Blog & the MCTS Self-paced training kit from Microsoft 70-431, it is not the case.
The rows in a clustered table are logically ordered by the key selected for the clustered index.
Refer “Types of Indexes” and “Clustered” section in the below blog for clarity:
I believe it can be modified as “Physically rearranges all the data pages that contain every row in the entire table.”
I also see that it is mentioned in detail in the next paragraph correctly, however, correcting the above would make it clear for everyone.
Please help me understand if I am interpreting your statement incorrectly.
Thanks in advance!
Thanks,
Adarsh
difference between composite index and single index ?
Hi i am curious to know what is the difference between
CREATE INDEX PIndex ON Persons (LastName) ,
CREATE INDEX PIndex ON Persons (DOB)
AND
CREATE INDEX PIndex ON Persons (LastName,DOB)
Hello Sir,
Could You please explain, why clustered indexes should be used on columns with low selectivity?
If I have a table with unique, identity column, where I keep for exemple a transaction number, I thought that clustered
index is the best for it. Each line is just put after the other one.
And if I have an index with low selectivity, and I want to do some insert / update / delete operations on that table, won’t it take more time
to do these operations?
I’am a little bit confused now …
Thank’s in advance!
Ewelina
how can i know “how performance is increased…?? “
1 Look at the difference in execution plan
2 Time reduction in the execution
Your articles would be easier to read if you ran them through a grammar checker, such as exists in Microsoft Word. I bought your book, and I’m hoping it is not as hard to read as your articles on this blog.
Hi Pinal,
Please confirm why include column does not increase the size of index as the column in include column stays at Leaf page.
Regards,
Neeraj
Hi Pinal,
First of all, I’m very thankful to you for such a brilliant way of helping newbies like me. I daily visit this site to keep myself in touch with SQL. Thanks for your knowledge share.
Just out of curiosity, i wanted to ask that if in your below example, we use a clustered index, instead of a Non-clustered one, wont there be a performance improvement?? As you said, Clustered indexes are mostly used for ranges??
Create following Index on Database AdventureWorks in SQL SERVER 2005
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO
Test the performance of following query before and after creating Index. The performance improvement is significant.
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN ‘98000’
AND ‘99999’;
GO
Hi Pinal,
Can u also embed the pictorial representation of both clustered and non clustered indexes. Which will be very helpful
Suppose we have a table “Employee” having column “age” in it. The table “employee” might have a data of different age groups according to their designations. The employee at high levels of post like Project Manager, Managing director etc has different age group when we are comparing it with other junior or senior level employees.
You have studied various indexing techniques for random access and better organization of data. By having a context of above scenario; if you have given an option to choose one indexing technique from two below indexes, which you would like to choose that will help to get optimized results?
§ Clustered index
§ Un-clustered/Non-clustered index
You have to choose only one technique out of the two and give valid reasons to support your answer.
please help me in this discussion
Hi Pinal,
This post helped me a lot. Thank you.
I would like to know how to use the include statement along with this syntax.
What I’m trying to do is moving the following index creation to inside table declaration.
CREATE NONCLUSTERED INDEX ix_id ON #tmp([ID]) Include(col1,col2,col3) with (fillfactor = 100)
I tried this
INDEX ix_id NONCLUSTERED ([ID]) Include(col1,col2,col3) with (fillfactor = 100)
But this gives some syntax error message. Could you please help?