Can you force SQL Server to use an index on computed column for a commutative operation?

Can you force SQL Server to use an index on computed column for an commutative operation?

We unfortunately have a table in SQL Server as follows

CREATE TABLE [dbo].[Data](
  [ID] [int] NOT NULL,
  [ValDate] [datetime] NOT NULL,
  [ValHour] [int] NOT NULL,
  [ValMin] [int] NOT NULL,
  [Value] [float] NULL,
  [Flag_ID] [int] NOT NULL,
  CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
  ([ID],[ValDate],[ValHour],[ValMin]) ON [PRIMARY]
) ON [PRIMARY]

Even though ValDate is a DATETIME, it only stores the date portion.

Some old applications using this database makes the following query

SELECT ID, ValDate, ValHour, ValMin, Value
FROM DATA
WHERE ID = @id 
  AND @start < DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) <= @end

This slows down with a lot of data, as SQL can't use the dates specified in the WHERE clause to seek and must scan each table entry with ID = @id to find all the rows.

We can't change the applications, so I decided to add a computed column and put an INDEX on it

ALTER TABLE dbo.Data ADD ComputedDateTime AS
  DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
GO
CREATE NONCLUSTERED INDEX [Data_ComputedDateTime_IDX] ON [dbo].[Data]
  ([ComputedDateTime], [ID])
INCLUDE ([ValDate], [ValHour], [ValMin], [Value])

This makes the original query fast, but if I change the order of the DATEADD's around, adding the minute to the date and then the hour the index is not utilized.

So I assume this is because SQL Server doesn't realize the operations are commutative i.e. (date + hour) + minute = (date + minute) + hour

Is there any way to speed up both computation orders, without creating two computed columns and two indexes?

As far as I know, your only options are to create the second computed column, contact the vendor of the software to ask for a fix, or to submit an enhancement request to Microsoft to get better support for your scenario. On the surface, the functionality that you're asking for could be viewed as simple: why can't SQL Server figure out that the expressions are equivalent when they obviously are to a programmer? However, it requires at least all of the following:

-
SQL Server needs to know that DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) is equal to DATEADD(hour, ValHour, DATEADD(minute, ValMin, ValDate)). There are a lot of date equivalences that SQL Server isn't yet aware of, and this is one of them.

-
The query optimizer would need to look for commutative matches during computed column matching.

-
The query optimizer would need to be able to find your match during the query optimization process, which is designed to very quickly give you a "good enough" plan.

I am sympathetic to your problem, but my guess is that the scenario that you're describing just isn't common enough for Microsoft to make improvements in this area, especially when there is a straightforward workaround that could be accomplished through changing code or by adding another computed column to the table.

StackExchange Database Administrators Q#221882, answer score: 4