29 ms·

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

• lazulicurio 6y ago
  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-constraints-with-indexed-views/ https://spaghettidba.com/2011/08/03/enforcing-complex-constr...
  • hultner 6y ago
    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.
    • lazulicurio 6y ago
      > 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.