This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is the best, most efficient setup?
We have a calendar event table that will quickly grow to millions of records.
The schema is something like:
CREATE TABLE [dbo].[CalendarEvent]( [Id] [uniqueidentifier] NOT NULL, [DtStart] [datetime] NULL, [DtEnd] [datetime] NULL, [Created] [datetime] NULL, [LastModified] [datetime] NULL, [CalendarEventType] [nvarchar](255) NULL, [CalendarId] [uniqueidentifier] NULL PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Forget about recurring events, etc. as that doesn't bear on our problem.
Most queries will be of the type:
select * from CalendarEvent where CalendarId = 'b5d6338f-805f-4717-9c0a-4600f95ac515' AND dtStart > '01/01/2020' AND dtStart < '10/22/2020'
Notice no joins, etc.
But we will also have some that select for individual events, and include joins:
select * from CalendarEvent ce join tags t on ce.Id = t.CalendarEventId where Id = '17606330-5486-496a-a91c-f5d0e123bfff'
Questions and ideas:
- Should we keep the Id as the PK, but make the start date the clustered index?
- Should we just make an index on dtStart?
- Should we partition by month?
- Should we denormalize a little and break duplicate the dtStart data by include year and month columns that we can index and use in our range queries?
In general, when you do your querying on a table by date range, what is the best setup for this type of table?
Note: If you think this question could be improved to help more people, make it more generic and widely applicable, such as removing references to a Calendar Event table specifically, and making this just about date range querying in any type of table, please help me do that.