Post reply

Using Included columns versus composite index

  • October 30, 2009 at 9:21 am

    #213067

    Can someone tell me what the difference is using a composite index versus using included columns and what the benefit is of using one over the other. I know that with a composite index you are storing all the data of the indexed columns and with the included columns you are not. But besides storage what is the difference?

    Thanks

  • October 30, 2009 at 9:26 am

    #1073218

    When you INCLUDE columns they are not ordered.


    Clear Sky SQL
    My Blog[/url]

  • October 30, 2009 at 9:34 am

    #1073225

    Okay. I understand that. What is the performance benefit of ordering versus non ordering the columns. If I have an index that has one column and 3 included columns. Versus an index with 4 columns what do I gain or lose from doing it one way or the other?

  • October 30, 2009 at 9:39 am

    #1073231

    Example:

    Index create on table Customer, field JoinDate, and include 3 fields: Name, surname, phone

    Following statement:

    select Name, surname, phone

    from Customer where Joindate between @startdate and @enddate

    will use ONLY your INCLUDE index to produce the result. There is no need to touch the table itself because all information is included in the index.

    Hope that help.

  • October 30, 2009 at 9:43 am

    #1073237

    I must be missing something. So using your example what is going to happen if I create an index on table customer, with Fields Joindate, Name, surname, phone

    with no included columns

  • October 30, 2009 at 9:43 am

    #1073238

    Performance will be better when inserting using included columns as no sort operation etc will take place on them. If they are only in the index to avoid a page lookup then i would include them, if they are required by a seek then dont.


    Clear Sky SQL
    My Blog[/url]

  • October 30, 2009 at 9:44 am

    #1073239

    Hi Pam

    INCLUDEd columns should be used if the additional columns are not part of the search criteria. They are especially a benefit when you work with many to many relations:

    CREATE TABLE T1

    (

    Id INT NOT NULL

    PRIMARY KEY CLUSTERED

    ,SomeData VARCHAR(100)

    );

    CREATE TABLE T2

    (

    Id INT NOT NULL

    PRIMARY KEY CLUSTERED

    ,SomeData VARCHAR(100)

    );

    CREATE TABLE T1_T2

    (

    T1Id INT NOT NULL

    ,T2Id INT NOT NULL

    ,PRIMARY KEY CLUSTERED (T1Id, T2Id)

    )

    CREATE INDEX IX_MyIndex ON T1_T2 (T2Id) INCLUDE (T1Id);

    Whenever you search all T1-Data for a specific T2-Row you just need the T2Id as search criterion. If you add the "T1Id" as indexed column to "IX_MyIndex" it will be maintained and ordered, what causes more effort for SQL Server. If you keep "T1Id" out of your index, SQL Server has to jump from your index to the primary key and to the data page to get the "T1Id". If you INCLUDE the "T1Id' to your index it is directly available.

    Greets

    Flo

    Edit: Typo in SQL...

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • October 30, 2009 at 9:45 am

    #1073240

    Okay. That makes sense.

    thanks for your help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply