Nice article. As I'm primarily a developer and have recently dipped my feet in the DBA world, I'd like to ask some questions around some points that I am unsure of.
1. Wouldn't the join between CalMonth and Sales.SalesOrderHeader be more efficient if you added a Foreign key relationship from Sales.SalesOrderHeader to the MonthID in CalMonth? There would have to be some initial update to history data to populate the data (in Sales.SalesOrderHeader), and the data could then be maintained, either through changes to the application, or by adding a (don't kill me yet, I know.. I hate them too, but they have their place) FOR INSERT and perhaps FOR UPDATE trigger (ONLY if an application code change is a barrier)?
2. You mentioned creating a covering index on Stay_ArrivalRevenue to avoid excessive key lookups. Would it not be better to create the index and only INCLUDE the fields to satisfy the SELECT portion?
i.e. Instead of
CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate, TotalRevenue)
CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate) INCLUDE (TotalRevenue)
This would preserve a better key density at higher levels of the index while still avoiding excessive key lookups.
As mentioned previously, I'm primarily a Dev and, at the moment, am still learning more and more about the DBA world. My points above are purely to help me understand better. Any insights would be appreciated.