CODEWITHSUNDEEP
sqlsql-serverdatabasequery-optimization
Keppy
Keppy

Reputation: 491

Query performance of non clustered index during inner join

We have two table in a inner join query. Which of the below options is recommended for high performance when there are millions of records in both the tables.

NOTE: We have a non-clustered index on the foreign key column. We don't have enough data to verify the performance in the development environment. Also there might be more tables come into this join with INNER or LEFT joins.

Tables:

Subscriber(SID(PK), Name)
Account(AID(PK),SID(FK), AName)

Query:

SELECT *
FROM Account A
INNER JOIN Subscriber S ON S.SubscriberID=  A.SubscriberID
WHERE 
S.SubscriberID = @subID -- option 1
A.SubscriberID = @subID -- option 2

Upvotes: 0

Views: 510

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

It should not make any difference which column you put the predicate on.

SQL Server can see the S.SubscriberID= A.SubscriberID condition and create an "implied predicate" for the other column anyway.

This does become a cross join (between the filtered rows from both sides) then as discussed here

Upvotes: 3

Related Questions