[lazulicurio]

> Well they are magical until you need to do something a bit more complex, like window functions.

The best method I've personally found for window functions is cross/outer apply + narrow indexes with included columns. A lot of times you can get away without an indexed view at all.

> you need to hint the server to actually use the indexed view or it will just use it as a regular view which I don’t understand the reasoning behind

SQL Server Enterprise will use indexed views automatically. But you gotta shell out the big bucks for that improved query planner.