Effective indexing and querying of overlapping date/time ranges

I have a table with the following schema:

AccountId: int
StartDateTime: datetime2(0)
EndDateTime: datetime2(0)

I want to be able to query this for rows with a certain AccountId, and whose date range intersects with a specified date range. E.g:

SELECT *
FROM ... AS d
WHERE d.AccountId = @AccountId
AND d.StartDateTime = @StartDateTime


This will be a very large table and I'll want to be able to query it as efficiently as possible. I've specified a single composite index of AccountId, StartDateTime, and EndDateTime.

Will this index, and the above query work effectively together? If not, what would be a better alternative? Thanks.

If the intervals within a particular account do not overlap with each other, you can replace your query with a TOP (1) query, to find the last start date before your desired end date. Do the same but opposite to get your earliest end date after your desired start date. These will form a small range you can use to check.

If they do overlap within a particular account, then you have one of the classically hard problems, and I'd recommend you read the material that Dejan Sarka has written at http://solidqblogs.azurewebsites.net/en/businessanalytics/interval-queries-in-sql-server-part-1/ (and its follow ups).

The problem is that an indexes on either startdate or enddate are less than ideal, because either one alone doesn't give a clue about whether the interval reaches your desired range. Another strategy is useful instead, such as indexing values within the range (lots of storage, and you have to consider the granularity), or come up with a creative solution such as interval trees or spatial analytics.

StackExchange Database Administrators Q#107092, answer score: 2