How should JOINed columns be included in an index? We are using SQL Server SE. Using a simple query, I would like to determine which index setup is best. The simple query is:

SELECT 
    COUNT(*) 
FROM 
    (PS LEFT OUTER JOIN LIS 
     ON (PS_ID = LIS_PS_ID AND LIS_DATE_PUBLISHED IS NOT NULL)) 
WHERE 
    PS_PER_ID_FIRST = 2000 
    AND PS_DATE_INSERTED &gt;= '1/1/2019' 
    AND PS_DATE_INSERTED &lt; '1/1/2020'


Notes: Columns ending in _ID are unique identifier INT columns. The PS table holds 2,500,000 records while the LIS table holds 200,000 records. If important, both tables grow organically and slowly, perhaps 20% per year.

It is important for me to get the indexes for this query correct. Normally, I would do this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED, PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID, LIS_DATE_PUBLISHED);


I have never been too familiar with when and why to specify columns as INCLUDEd. I know to specify them as INCLUDEd when they are SELECTed but I'm never sure about the columns in the JOIN statements. In other words, should the indexes instead be like this:

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST, PS_DATE_INSERTED) INCLUDE (PS_ID);
CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED) INCLUDE (LIS_PS_ID);


Or, if I'm off my rocker and there is a much smarter way of going about the indexing for this query, please let me know.
Including columns is done when you don't want to be able to search on them through the root and intermediate nodes of the index. 

As a result only the leaf level pages have the data that is included.

Wether you put the join columns in the key columns depends on the filters used.

Filtered index

Based on your query, the best index in theory would be a filtered index (assuming no parameterization).

CREATE INDEX IX_PS_1 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED )
WHERE PS_PER_ID_FIRST  = 2000 
      AND PS_DATE_INSERTED &gt;= '1/1/2019' 
      AND PS_DATE_INSERTED &lt; '1/1/2020';

CREATE INDEX IX_LIS_1 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED)
WHERE LIS_DATE_PUBLISHED IS NOT NULL;


Depending on the data, a merge join (join that can use 2 sorted inputs) could be chosen.
This join will be best when comparing large datasets. 
The stored index data matches the filters exactly.





If filtered indexes are a no go

DEPENDS ON YOUR DATA 

Now, when comparing &gt;= and &lt; in your where clause, it will probably not matter whether PS_ID is included or not in terms of performance as it will not be able to search on it.

As such these indexes should do

CREATE INDEX IX_PS_1 ON PS (PS_PER_ID_FIRST,PS_DATE_INSERTED )
INCLUDE(PS_ID);

CREATE INDEX IX_LIS_1 ON LIS (LIS_DATE_PUBLISHED,LIS_PS_ID);


This gives the seeks on the filters, but a hash join instead of a merge join with my data.



Again, depending on your data &amp; how much is filtered by the applied filters, these indexes could work too:

CREATE INDEX IX_PS_1_3 ON PS (PS_ID)
INCLUDE(PS_PER_ID_FIRST,PS_DATE_INSERTED);
CREATE INDEX IX_LIS_1_3 ON LIS (LIS_PS_ID)
INCLUDE(LIS_DATE_PUBLISHED);


With my data, nothing is filtered out and the optimizer knows to use a merge join with residual predicate on the filters.



This is an extreme example but shows the differences and why it depends™