Index on SQL Tables that are in Join?? 2

[RovirozM]

Hi guys,

Right now I know this:

SELECT *
FROM TABLE
WHERE CAMPO1 = 'Something' AND CAMPO2 = 'Other'

If I used this Query without index it will do a Full Scan in my table to find my two conditions... But If I add an Index with this two fields it will be an index scan (faster) and I see the difference on the execution plan.

My question is? what about to do? when I have two Join Tables, for example:

SELECT *
FROM TABLE1 T1 INNER JOIN TABLE2 T2
ON T1.CAMPOT1 = T2.CAMPOT2
WHERE T1.CAMPO1 = 'Something' AND T2.CAMPO2 = 'Other'

As you can see my conditions are two but for different tables, in this case what I have to do in order to improve my index here? One index for Table1 with Campo1 and one index for table2 with Campo2 only? Or do I have to consider what I have on my "ON" expression?

Thanks for any suggestions!

MR

 

[markros]

You need to add in index in Table1 on Campo1 field and in Table2 on Campo2 field.

PluralSight Learning Library

 

[SQLSister]

If one of your fields is a PK, it is already indexed, but Fks are not, so you need to make sure that one is indexed as well.

"NOTHING is more important in a database than integrity." ESquared

 

[TheBugSlayer]

In general, in addition to the suggestions by the other members, indexing the fields in your WHERE clause will improve the efficiency of your query. So the number of JOIN statements (or tables in the query) should not matter. Of course too many JOINs can become a problem, but that' s a different ball game.

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[markros]

Check this discussion also

http://forum.lessthandot.com/viewtopic.php?f=17&t=12294

PluralSight Learning Library