In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the
ID column on the table.
CREATE TABLE [Measurement] ( [ID] INT NOT NULL PRIMARY KEY NOT NULL, [ParameterID] INT NOT NULL, [Measuretime] DATETIME NOT NULL, [Value] FLOAT NOT NULL, CONSTRAINT [FK_Measurement_Parameter] FOREIGN KEY ([ParameterID]) REFERENCES [Parameter]([ID]) ) CREATE INDEX [IX_Measurement_Measuretime_ParameterID] ON [Measurement] ([Measuretime]) INCLUDE ([ParameterID]); CREATE INDEX [IX_Measurement_ParameterID_Measuretime] ON [Measurement] ([ParameterID]) INCLUDE ([Measuretime]);
Our data gets written in 1-5 second intervals with successive timestamps for all Parameters.
We decided that it would probably be a better idea to create the clustered index on
Measuretime as most queries are on those to columns.
Here are some example how most our queries look like:
SELECT * FROM Measurement WHERE ParameterID = 1 and Measuretime between '2015-01-24' and '2015-01-25'
SELECT ParameterID, cast(Measuretime as date), avg(value) FROM Measurement WHERE ParameterID = 1 and Measuretime between '2015-01-01' and '2015-02-01' GROUP BY ParameterID, cast(Measuretime as date)
SELECT DISTINCT ParameterID, FIRST_VALUE(cast(Measuretime as date)) OVER (PARTITION BY cast(Measuretime as date), ParameterID ORDER BY Measuretime ) Measuredate, PERCENTILE_CONT(.25) WITHIN GROUP(ORDER BY Value) OVER (PARTITION BY cast(Measuretime as date), ParameterID) as [q1], ... FROM Measurement WHERE Measuretime between '2015-01-01' and '2015-02-01' -- and ParameterID = 1 ORDER BY Measuretime, ParameterID
Which of those three ways that come to my mind for
INDEX creation is the best suited for such a scenario?
CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([Measuretime],[ParameterID])as this is also the order data gets written, and both columns are queried.
CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([ParameterID],[Measuretime])as nearly all our queries need to filter by
ParameterIDin one way or another and only afterwards
- On only one of those to and go for a normal
INDEXfor the other column.