Yep! And they're pretty magical. Beyond materializing joins, you can use COUNT_BIG to greatly speed up common DISTINCT queries and the two row trick[1] to enforce complex constraints.
Well they are magical until you need to do something a bit more complex, like window functions, where they’ll not work again. I’ve worked around this by using indexed views for sub parts of a larger regular view but it feels a bit hacks since 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.
> 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.
But for simple things they’re magical.