Why is SQL Server suggesting to include predicate columns in the INCLUDE of a new index?

139 views Asked by At

I have a large query that I'm optimising and it requires an index. I created one that I thought was right, but SQL Server is telling me different. I have extracted the part of the query that needs the index and this sample produces the same index warning:

SELECT  CompanyTransaction.Amount
        -- columns from other tables..
FROM Company
    -- JOIN other tables
LEFT JOIN CompanyTransaction
    ON CompanyTransaction.CompanyId = Company.Id
    AND CompanyTransaction.Type = 'SR'
    AND CompanyTransaction.Code = 1000

As I'm filtering on CompanyId, Type and Code I presumed they would be the key columns. And as I'm selecting the Amount column I included that in the INCLUDE. The index I put together is the following:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CompanyTransaction] ([CompanyId],[Type],[Code])
INCLUDE ([Amount])

But the index SQL Server is suggesting I create is:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CompanyTransaction] ([Type],[Code])
INCLUDE ([CompanyId], [Amount])

This isn't the first time this has happened. Queries in the past have had similar suggestions but I've never looked into it too much. Can anyone provide reasoning for why SQL Server thinks the CompanyId would be better served in the INCLUDE when it's being searched on?

Many thanks!

0

There are 0 answers