October 30, 2009 at 9:21 am
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
When you INCLUDE columns they are not ordered.
October 30, 2009 at 9:34 am
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
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
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
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.
October 30, 2009 at 9:44 am
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...
October 30, 2009 at 9:45 am
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