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
Pinal,
Im not able to figure out the difference between a composite index and an index with included columns? It would be helpful to me if you explan this. Thanks….
Hey,
Are the Included Column Index along with Clustered Index and Non-clustered Index same as covering indexes … or there is any difference in them ????
Hi ,
How do we decide whether to create non clustered index on a column.?
I have a scenario where query response is taking nearly 2 minutes for <100 rows.
The major tables A B used in joining contain A-94million rows and B-4 million rows.
B.clientid primary key — 4million
A.clientid — distinct values — 2 million
So is it worth to create non clustered index on A.clientid column on A Table(94 million rows) ?
Will the join be faster and response better?
Appreciate your help !
Thanks
Sudha
Hi Jivan,
from above lines we can conclude
Composite index will increase the size of index as we add more columns to non clusuterd index where as we use Include which will not increase the number of index key columns or index key size limitations.
” 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.”
Hi Nidhi
Included Column Index is for non clustered index only. to some level these are Covering Index but by-pass the limitation of earlier version of sql server as these don’t conunt for the size of non clustered index.
Hi Sudha;
Creating index is a real challenge for some one. one needs to be carefull while creating index.
answer to your question How to decide to create non clustered index is simple.
if u want to get only one record then create non cluseterd index but if your query needs a range of records then better to create clustred index.
actually u need to see which type of query is accssing data more oftenly and then desing index accodingly.
Case: the other day i have a table with 9 M records
it has a PK on TransactionNo.
but i found no body is concerend about TransactionNo, every one is accessing data on TransactionDate column, giving range like where TransactionDate >= getdate() -30 and TransactionDate <=getdate()
so my query was not using index properly. what i did i made PK ( whihc will be clustred index) on TransactionDate plus TransactionId.
and i am enjoying good performance.
Any one else can help me in this Case.
pinal dave,
Your article is very informative. As a reader i’ve few suggestions for you.
1) Please increase the font of SQL Code – gives more readability
2) Provide the color coding scheme of SQL Server so that we can easily understand the code snippet. There are may plugins for writers like Windows Live Writer to provide color schemes for code.
Anyway, your article is very informative. Thanks a lot.
hi
i ve created NC index on 2 tables, so while joining this two tbles, i get the result set, but while performing the Execution plan, the index of first table is considered, y so?
please reply me ASAP
thanks
how to delete clustered index
drop index tablename.indexname
drop index Tablename.indexname
If i do a sp_helpindex TABLE_NAME , I get the list of indexes on the table with thier columns, however, INCLUDED columns are not shown. is there a command to display index definitions along with their included columns?
Thanks,
Jim
If I have a non-clustered index within the limitations of 16 fields and under 900 bytes as opposed to having 1 field with 15 included fields for the same – what is the difference in performance? Is there any advantage in using the included columns?
If I have more then 16 fields in a table that I want to index can I create a second index?
How does this affect performance?
Thanks,
Dave
Would you mind explaining me below:
I can make three primary keys in a table like
CREATE TABLE ORDER (
order_id bigint NOT NULL,
product_id int NOT NULL,
order_date datetime NOT NULL,
price decimal(21,2) NOT NULL,
———– columns —————
PRIMARY KEY (order_id, product_id, order_date)
)
GO
WELL, i can see three PK’s in SQLMS. Are these really 3 Pk’s or 1 PK or 2 composite keys?
As we create PK, we create an automatic Clustered index on the PK column.
What would be in this case?
Would there be 3 Clustered indexes here?
When i created this table and checked out the script by SQL server, i got script seems to have clustered index on a table instead of 3 above mentioned columns, so what does it mean?
i will appreciate your help..
Leo
Well you can create three primary key on columns, but one will turn a primary key and the rest is composite key.
But the one with the primary key becomes the column to which by default the Clustered indexes is created.
This is very helpful to me. Pl tell me how to test keys and indexes in SQL Server2005.
I have create composite Primary key constraint but how to drop the constratint how it is possible ?
CREATE TABLE ORDER1 (
order_id bigint NOT NULL,
product_id int NOT NULL,
order_date datetime NOT NULL,
price decimal(21,2) NOT NULL,
PRIMARY KEY (order_id, product_id, order_date)
)
alter table Order1 drop constraint
actually wirite this syntax drop constraint and constraint name but above samplee how to identify the constraint name
alter table employee drop constraint id_pkey
HI,
Can you please let me know the use of INCLUDE and what parameter to be used in INCLUDE (parameter)
hello pinal sir
actually m new with sql server m a student just learning sql
i have a question that in which case sql creates heap table?
or table is created as heap table?
please bear with me sir.
thank you so much for your site..
m getting lot of hepl from this site.
Hi Supriya,
A heap is a table without a clustered index. read more at the below BOL link:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72f63db6-7306-4c43-a73d-7eaa4ffe1f82.htm
Note: you will need to paste this link in BOL.
Start>Programs>Microsoft SQl Server 2005>Documentation and Tutorials> SQL Server Books Online
Dear Dave,
Thanks for your prompt response.
I need to understand the following thing:
1) What will the data pages contains and how it appears?
2) What is meant by Key and non key columns.
3) Is there any diagramatic explanation on index topic.
Please share the answers for the above questions.
Regards
Denison.s
1) Data pages contains actual data in your tables. Data is arranged into pages just like “normal” memory paging works.
My guess is that in 90% of cases you don’t have to think about paging and if you do, hire a consultant :)
2) Key columns and non-key columns. Let’s consider the following SQL statement:
CREATE NONCLUSTERED INDEX [IX_Foo] ON [dbo].[Table]
( Column1, Column2)
INCLUDE (Column3, Column4)
This is called covering composite index (I think). Key columns are Column1 and Column2. They can be used, for example, as part of WHERE and JOIN clauses.
Non-key columns are Column3 and Column4. They can be used in SELECT fields. So for example:
SELECT Column3, Column4 FROM [dbo].[Table] WHERE Column1 = 1 AND Column2 = 2
hi sir
how are you
actually m new with sql server m a student just learning sql
i have a question that
1.what and how many type of indexing in SQL server???
i found 3 type that as i understand on your articles
1-Clustered Index
2-Non-Clustered Index
3-Included Column Index (New in SQL Server 2005)
another questions
2.What the different of indexing between the SQLserver 2005 and SQL server 2008??
3.What the differnt of INDEXING on microsoft SQL sever and Oracle DB ??
please bear with me sir.
thank you so much for your site. AND that help me.
m getting lot of hepl from this site.
Dear sir
I tried a lot on non clustered index with include non key columns… But i have still problem of (Warning! The maximum key length is 900 bytes)…
please help me…
i have 6 key column…and 5 non key columns……
please Help..