A tool I find I often tell people that really should have already implemented in their environment is a Calendar table, however, something I don't actually have to show them is my own example. As such I felt it time to provide one, so that next time someone asks me "what should it look like", I can demonstrate it.
The Calendar Table I have in my Utility database is actually all most entirely made up of computed columns; the only column that isn't is the Calendar Date. With the exception of the name columns as well, all the columns are indexable (the weekday with thanks for SQL Server 2022's DATETRUNC function), as they're all deterministic.
I provide some example indexes the table well, however, index the table as much as you want/need as that's part of the point. Calendar tables aren't going to change; February isn't going to suddenly have 30 days for example. As such the "pain" of INSERTing/UPDATEing a table with indexes is non-existent; focus on the speed of reading.
Feel free to use this Calendar Table as a template for your own solutions.
CREATE TABLE dbo.[Calendar] ([CalendarDate] [date] NOT NULL,
[JulianDate] AS ((DATEPART(YEAR, [CalendarDate]) * (1000) + DATEDIFF(DAY, DATEFROMPARTS(DATEPART(YEAR, [CalendarDate]), (1), (1)), [CalendarDate])) + (1)),
[CalendarYear] AS (DATEPART(YEAR, [CalendarDate])),
[CalendarMonth] AS (DATEPART(MONTH, [CalendarDate])),
[CalendarDay] AS (DATEPART(DAY, [CalendarDate])),
[CalendarMonthName] AS (DATENAME(MONTH, [CalendarDate])),
[CalendarWeekDay] AS (DATEDIFF(DAY, DATETRUNC(ISO_WEEK, CalendarDate), CalendarDate) + 1),
[CalendarDayName] AS (DATENAME(WEEKDAY, [CalendarDate])),
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED ([CalendarDate] ASC))
GO
INSERT INTO dbo.Calendar (CalendarDate)
SELECT DATEADD(DAY, GS.value, '19000101')
FROM GENERATE_SERIES(0, DATEDIFF(DAY, '19000101', '20991231')) GS;
GO
CREATE NONCLUSTERED INDEX [IX_Calendar_Day]
ON dbo.[Calendar] ([CalendarDay] ASC)
INCLUDE ([JulianDate], [CalendarYear], [CalendarMonth], CalendarWeekDay);
GO
CREATE NONCLUSTERED INDEX [IX_Calendar_Month]
ON dbo.[Calendar] ([CalendarMonth] ASC, CalendarDay ASC)
INCLUDE ([JulianDate], [CalendarYear], CalendarWeekDay);
GO
CREATE NONCLUSTERED INDEX [IX_Calendar_Year]
ON dbo.[Calendar] ([CalendarYear] ASC, [CalendarMonth] ASC, [CalendarDay] ASC)
INCLUDE ([JulianDate], CalendarWeekDay);
GO
CREATE NONCLUSTERED INDEX [IX_Calendar_WeekDay]
ON dbo.[Calendar] (CalendarWeekDay ASC)
INCLUDE ([JulianDate], [CalendarYear], [CalendarDay], CalendarMonth);
GO
CREATE NONCLUSTERED INDEX [IX_Calendar_YearWeekDay]
ON dbo.[Calendar] ([CalendarYear] ASC, CalendarWeekDay)
INCLUDE ([JulianDate], [CalendarMonth], CalendarDay);If you want the weekday to start on a day other than Number, then you can use some "integer maths". For example, to start it on a Sunday you could do (((DATEDIFF(DAY, DATETRUNC(ISO_WEEK, CalendarDate), CalendarDate))+1)%7)+1 AS USWeekDay.
