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?
