SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

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 (http://blog.SQLAuthority.com) , All the examples above are taken from BOL.

About these ads

44 thoughts on “SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

  1. 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….

  2. 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 ????

  3. 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

  4. 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.

  5. 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.

  6. 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.

  7. 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

  8. 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

  9. 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?

  10. 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

  11. Pingback: SQL SERVER - Introduction to Heap Structure - What is Heap? Journey to SQL Authority with Pinal Dave

  12. 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.

  13. 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

  14. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

  15. 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.

  16. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  17. 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

  18. 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

  19. 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.

  20. 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..

  21. 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

  22. 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.

  23. 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.

  24. 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 …

  25. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31 Journey to SQLAuthority

  26. 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:
    http://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/

    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

  27. 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)

  28. 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

  29. 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.

  30. 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

  31. 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

  32. Pingback: SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 5 « SQL Server Journey with SQL Authority

  33. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  34. Hi Pinal,
    Can u also embed the pictorial representation of both clustered and non clustered indexes. Which will be very helpful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s