Hacker News Reader

Comment by Mouse47 | Hacker News Reader

For what it's worth SQL server supports incremental updates for materialized views. I think they're called indexed views, though

View on HN

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.

[1] https://spaghettidba.com/2011/08/03/enforcing-complex-constr...

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.

But for simple things they’re magical.

> 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.

Reply on news.ycombinator.com