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 &lt; DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate))
  AND DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) &lt;= @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.