Index usage in joins

Transact-SQL
1

Hello,
Can someone tell me if an composite index can be used to optimize both the join condition and the where clause?
Like in:
Select * From Invoices i
Inner Join InvoiceDetails d on i.Id=d.InvId
Where i.ProductId=@id And i.Qty > 0
I mean: indexing InvoiceDetails By InvId,ProductId,Qty helps? Considering InvId is used by the Join clause and the rest by the Where clause.
Thank you,
Daniel

2

hi

please see the below link .. and see what you are looking for ( hope it helps )

https://www.sqlshack.com/impact-of-the-column-order-in-composite-index-sql-server/

3

Thank you harish but I know what a composite index is and how the order of columns works.
The question is if an join condition and where clause both can take advantage of the same composite index.

5

I did search and I didn't find anything. It is not a trivial problem like "what is a composite index"....

That's why I am asking here and answers like "you have to search for it" are not helping.

1 Like
6

did you create the index and see the execution plan ...in the execution plan if its being used ...

7

I yield to @ScottPletcher and @jeffw8713 on this.

8

what i meant by "you have to search for it"

sometimes in my experience the gotcha's and certain scenarios are there in the documentation
it takes some digging its NOT pleasant to dig

i do not know it RIGHT OFF the bat ... other than that NOTHING else

9

No, I did not since I was hoping someone would help me with an straight answer

10

The ProductId and Qty as the keys in an index will definitely help. Including the Id might help for the join to InvoiceDetails or it might not, but it's worth trying it, esp. since the Id will make the index unique (processing strongly benefits from unique indexes).

CREATE UNIQUE NONCLUSTERED INDEX Invoices__IX_ProductId ON dbo.Invoices ( ProductId, Qty, Id ) WITH ( FILLFACTOR = 95 );
11

Hi, Scott,
So it might help, there’s no rule. OK, thank you
The order of the fields in the index tells me the WHERE clause will be evaluated first (to narrow the range) and the JOIN after. Interesting, usually I put the ID first.

12

I get that, but SQL evaluates the WHERE clause before any JOIN(s). And that makes sense, because the WHERE reduces the number of rows that need processed.

SQL also favors "=" over ">". That makes sense too, since "=" is far more specific and limits row more.

13

The reason it might or might not help the JOIN is because the number of rows to be looked up often makes the biggest difference in how SQL does the lookup, and SQL won't know that until it has found the matching rows in the first table. For example, say 40% of the rows in Invoices matched, then SQL would almost certainly scan the InvoiceDetails table (assuming it was clustered by identity or some typical, but wrong, clustering), because using a nonclustered index with lookups to the main table would be vastly too much overhead.

Edit, added: If only 1% of the rows in Invoices matched, then SQL will almost certainly use a nonclustered index to find the InvIds, and then use the clustering key to read back to the clustered index, since that will still be less overhead than scanning the entire InvoiceDetails table.

The "SELECT *" also would often force SQL to do a scan, since it has to retrieve all the columns in the table.

1 Like
14

Thank you, Scott