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

by Jason   Last Updated June 18, 2018 20:06 PM

SQL Server 2016

I have the following query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    (   [ValidFrom] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )
     AND (   [DataEffective] <= SYSDATETIMEOFFSET() 
         AND SYSDATETIMEOFFSET() < 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] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET() 
     AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
     AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))

and like this:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET() 
     AND (   [ValidUntil] > SYSDATETIMEOFFSET()
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > SYSDATETIMEOFFSET()
         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


Related Questions


Help tune query

Updated February 01, 2017 14:02 PM




Using DTA vs. evaluating DMVs?

Updated April 21, 2017 04:06 AM