Should I include a column I'm joining to in my index?

Question

Should I include a column I'm joining to in my index?

rbrenan

SSC Rookie

Points: 28
More actions
March 2, 2010 at 6:18 am

#217134

I have a statement similar to:
SELECT
column1_output, column2_output
FROM
table1 t
INNER JOIN
containstable(table1, column1_search, '"searchterm"') ct ON ct. = table1.id
I have a nonclustered index on column1_search with included columns column1_output and column2_output. Would the join to the contains table go quicker if I also included id in the index? I'm not sure if columns that are used in joins but are not selected should be included in nonclustered indexes.
Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

rbrenan (3/2/2010)
I have a statement similar to:
SELECT
column1_output, column2_output
FROM
table1 t
INNER JOIN
containstable(table1, column1_search, '"searchterm"') ct ON ct. = table1.id
I have a nonclustered index on column1_search with included columns column1_output and column2_output. Would the join to the contains table go quicker if I also included id in the index? I'm not sure if columns that are used in joins but are not selected should be included in nonclustered indexes.
Thanks!

It may make a difference, but it depends on a lot of details. Generally, including search columns, including Join columns is the first thing that goes in an index, then the SELECT columns can be added with INCLUDE, if needed. However, CONTAINSTABLE() behaves differently than a lot of other functions in search conditions, so I am not really sure in your case. In any event, you should always test it to be sure.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

in which column you have UNIQUE index ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

rbrenan SSC Rookie Points: 28 More actions · Answer 3

The unique index is the id column that the CONTAINS table's key column joins to.