March 5, 2014 at 12:53 pm
I understand the use of include option in index. However, the index is supposed to point to the location of the record on the table.
Would there be any benefit to include columns that are part of the primary key on a non-clustered index, even if I don't intend to add it to the select statement but use it for joins with other tables?
Thanks
March 5, 2014 at 1:02 pm
N_Muller (3/5/2014)
Would there be any benefit to include columns that are part of the primary key on a non-clustered index, even if I don't intend to add it to the select statement but use it for joins with other tables?
If the query(s) you're creating the index for needs those columns, specify them. If it doesn't, leave them out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2014 at 1:03 pm
N_Muller (3/5/2014)
Would there be any benefit to include columns that are part of the primary key on a non-clustered index, even if I don't intend to add it to the select statement but use it for joins with other tables?
You'll have to be more explicit about what you have and your intent. Is your Primary Key on that table also the clustered index? Then no, it's already included. If the PK is nonclustered, maybe.
But, not for joining TO this table. You'd include columns in this table that are the PK/lookup in OTHER tables. You need the PK in the index itself if you're using it for joins to that table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 5, 2014 at 1:04 pm
That's what I thought. Thanks, Gail.
March 5, 2014 at 1:12 pm
Let me be more specific with an example.
create table A
(
IndexID int identity(1,1),
Col1 int,
Col2 varchar(30),
constraint PK_A primary key clustered ( IndexID )
)
go
create table B
(
IndexID int identity(1,1),
TblA_IndexID int,
ColX varchar(100),
ColY varchar(50),
constraint PK_B primary key clustered ( IndexID )
)
go
My query is:
select B.IndexID, B.ColX, B.ColY
from A inner join B on B.TblA_IndexID = A.IndexID
where A.Col1 = 10
My indexes on table A are:
create index IX_A_Col1 on A ( Col1 )
go
create index IX_A_Col2 on A ( Col2 )
go
The question is: Should index IX_A_Col1 be changed to:
create index IX_A_Col1 on A ( Col1 ) include ( IndexID )
even if A.IndexID is not part of my query select statement?
March 5, 2014 at 1:23 pm
I would create, as an index for that query:
create index IX_A_Col1 on A ( Col1, IndexID )
Having it as a second key column is more useful than having it as an include for the purposes of the join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2014 at 1:23 pm
No, the clustered index is already included in all nonclustered indexes. The include would be superfluous unless you're trying to protect against someone changing the clustered index in the future.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 5, 2014 at 1:24 pm
GilaMonster (3/5/2014)
I would create, as an index for that query:create index IX_A_Col1 on A ( Col1, IndexID )
Having it as a second key column is more useful than having it as an include for the purposes of the join.
Hm, Gail, would you be willing to expound on that? That's not how I understood it works, so apparently I need to learn something today. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 5, 2014 at 1:28 pm
Evil Kraig F (3/5/2014)
No, the clustered index is already included in all nonclustered indexes. The include would be superfluous unless you're trying to protect against someone changing the clustered index in the future.
I've already had one long discussion about this today...
If a column is needed in an index it should be explicitly added. SQL's internal behaviour should not be depended upon, nor should an index design assume that other indexes on the table are not going to be changed. There's no penalty for being explicit and allowing implicit behaviour to dictate index structure leaves a risk of unexpected performance changes in the future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2014 at 1:31 pm
Evil Kraig F (3/5/2014)
GilaMonster (3/5/2014)
I would create, as an index for that query:create index IX_A_Col1 on A ( Col1, IndexID )
Having it as a second key column is more useful than having it as an include for the purposes of the join.
Hm, Gail, would you be willing to expound on that? That's not how I understood it works, so apparently I need to learn something today. 🙂
Having IndexID as the second column means that, within values of Col1, the values are sorted by IndexID, allowing the possibility of a merge join. It also allows SQL to use this as the inner table of a nested loop join, doing a two-column seek for each IndexID in the outer table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2014 at 1:37 pm
GilaMonster (3/5/2014)
Evil Kraig F (3/5/2014)
GilaMonster (3/5/2014)
I would create, as an index for that query:create index IX_A_Col1 on A ( Col1, IndexID )
Having it as a second key column is more useful than having it as an include for the purposes of the join.
Hm, Gail, would you be willing to expound on that? That's not how I understood it works, so apparently I need to learn something today. 🙂
Having IndexID as the second column means that, within values of Col1, the values are sorted by IndexID, allowing the possibility of a merge join. It also allows SQL to use this as the inner table of a nested loop join, doing a two-column seek for each IndexID in the outer table.
D'oh, well, alrighty then. Now that you spell it out I can see it. Thanks!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply