What index can I create to help a query using multiple datetime columns and datetime functions in its where clause? SQL Server 2016

I have the following query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    (   [ValidFrom] &lt;= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() &lt; COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )
     AND (   [DataEffective] &lt;= SYSDATETIMEOFFSET() 
         AND SYSDATETIMEOFFSET() &lt; COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )


I'm getting a table scan every time I run this query. I need an index on this table because this query will be run a lot. I've tried several different ones but I can't seem to get the server to use any of them.

I've tried indexing:


ValidFrom
ValidFrom, DataEffective
ValidFrom, ValidUntil, DataEffective, DataExpired


I've also tested against both an empty table and a table seeded with 10202 rows.

I've also tested with a restructured query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] &lt;= SYSDATETIMEOFFSET()
     AND [DataEffective] &lt;= SYSDATETIMEOFFSET() 
     AND SYSDATETIMEOFFSET() &lt; COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
     AND SYSDATETIMEOFFSET() &lt; COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))


and like this:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] &lt;= SYSDATETIMEOFFSET()
     AND [DataEffective] &lt;= SYSDATETIMEOFFSET() 
     AND (   [ValidUntil] &gt; SYSDATETIMEOFFSET()
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] &gt; SYSDATETIMEOFFSET()
         OR  [DataExpired] IS NULL
         )


and like this (to remove the non-deterministic SYSDATETIMEOFFSET() reference):

DECLARE @Now DateTimeOffset(7)

SET @Now = SYSDATETIMEOFFSET()

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] &lt;= @Now
     AND [DataEffective] &lt;= @Now
     AND (   [ValidUntil] &gt; @Now
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] &gt; @Now
         OR  [DataExpired] IS NULL
         )


Table Definition

CREATE TABLE [dbo].[Networks]( 
[ValidFrom] [datetimeoffset](7) NOT NULL, 
[ValidUntil] [datetimeoffset](7) NULL, 
[DataEffective] [datetimeoffset](7) NOT NULL, 
[DataExpired] [datetimeoffset](7) NULL, 
[UserId] [nvarchar](128) NOT NULL, 
[Id] [uniqueidentifier] NOT NULL, 
[Descriptor] [nvarchar](255) NULL ) 
ON [PRIMARY] 
GO
I think you can benefit from rewriting this query first, plunking it into a TVF and adding a non-clustered index on ValidFrom and DateEffective.

create nonclustered index ix_networks_validfrom_dateeffective
    on dbo.Networks (ValidFron, DateEffective)
    include (Id, Descriptor);
go

create function dbo.ValidNetworks (
    @dt datetimeoffset(7)
)
returns table
as 
    return (
        with networks as (
            select 
                Id
                ,Descriptor
                ,ValidFrom
                ,DateEffective
                ,isnull(ValidUntil, dateadd(day, 1, @dt)) as ValidUntil
                ,isnull(DateExpired, dateadd(day, 1, @dt)) as DateExpired
            from 
                dbo.Networks
        )

        select
            Id
            ,Descriptor
        from
            networks
        where
            ValidFrom &lt;= @dt
            and DateEffective &lt;= @dt
            and ValidUntil &gt; @dt
            and DateExpired &gt; @dt
    );


You could also create computed columns for ValidUntil and DateExpired and eliminate the CTE from the TVF.

I would also be curious to see this split into two separate queries and union'd. Whereby in both queries you filter on ValidFrom and DateEffective. In one you filter for null values in ValidUntil and DateExpired, and the other compare against the @dt value.