CREATE TABLE [dbo].[TimeValues] ( [Time] DATETIME NOT NULL, [Collection_Id] INT NOT NULL, [Value] DECIMAL(18,6) NOT NULL, CONSTRAINT [PK_TimeValues] PRIMARY KEY ([Time],[Collection_Id]) ON [MyPartitionScheme]([Time]) ) ON [MyPartitionScheme]([Time]) --The following script takes 2 min to run ??? SELECT DATEADD(DAY,DATEDIFF(DAY,'2000-01-01 00:00:00',[Time]),'2000-01-01 00:00:00') AS [Time] ,1 AS [Collection_Id] ,SUM([Value]) AS [Value] FROM [dbo].[TimeValues] WHERE [Time] BETWEEN '2000-01-01' AND '2020-01-01' AND Collection_Id = 1 GROUP BY DATEDIFF(DAY,'2000-01-01 00:00:00',[Time]) --If I add the following Index the script takes 0 sec. to run WHY ???? CREATE NONCLUSTERED INDEX [IX_TimeValues_Collection_Id_Time] ON [dbo].[TimeValues]([Collection_Id],[Time]) INCLUDE ([Value]) WITH (FILLFACTOR = 80)
Time
Collection_Id
[Time] BETWEEN '2000-01-01' AND '2020-01-01'
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)