Performance of a self join in a temporal table with a single day's difference between end_date and start_date Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an employee_id as well. And there are gaps in the sequence. The interval can vary.

The clustered index is on employee_id, start_date.
There is an unclustered index on employee_id, end_date that isn't used by the query below.

The current join from the current record to the previous contiguous record is (simplified):

SELECT *
FROM self AS current 
LEFT OUTER JOIN self AS previous 
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date


There is a Seek Predicate on the employee_id. The DATEADD() is nonSARGable and stops the end_date index being used, so the join from start_date to end_date is a non-indexed Predicate.

Is there a way to get the start_date to end_date join to use an index? Either by clever indexing, a view or by rewriting the query?

I'm still on SQL Server 2008 R2, but please feel free to use the latest version in the answers.
SELECT *
FROM self AS current 
LEFT OUTER JOIN self AS previous 
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

and previous.start_date &lt;&gt; current.start_date


or if has to be less  

and previous.start_date &lt;  current.start_date


or if can isolate on prior  

and previous.start_date = LAG (current.start_date, 1, 0) OVER (PARTITION BY current.employee_id ORDER BY current.start_date DESC)