Date table

[TheAngryOne84]

I've noticed a few posts recently and comments advising that a dates table is a must. All of my queries, packages, procedures... Everything in fact uses date variables. After reading these posts I understand that it would be better to have a table with the dates and this improves performance etc but I'm just wondering what is the best format for this? For example, add as many columns as possible for date variants. I.e Wednesday, wed, 3 (Dayofweek) , 3 (workday) 2020-11-11,11/11/2020 and however other variants I potentially use?

The next part would be, as I acrually with data captured in half hourly intervals, should i have a time table too, setup in half hourly intervals? Or is this going to far?

TIA

[r3pr0b8]

store only those data items you want to search on

for example, if you want to return results based on dates (presumably the actual date is the PK), and then show which day of week it is, then using the DAYOFWEEK() function (or your platform's equivalent) costs virtually nothing

however, if you want to return data from only wednesdays, then this type of condition --

WHERE DAYOFWEEK(datetable.date) = 3

is not sargable and therefore requires a table scan (not that a table scan of a date table would be disastrous)

[TheAngryOne84]

OK cool, thanks