Re:Some SQL Tricks of an Application DBA
by Mouse47 on Wednesday July 29, 2020 @08:38PM
(#23993132)
For what it's worth SQL server supports incremental updates for materialized views. I think they're called indexed views, though
[ Parent
| on HN ]
Re:Some SQL Tricks of an Application DBA
by lazulicurio on Wednesday July 29, 2020 @09:48PM
(#23993485)
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.
Re:Some SQL Tricks of an Application DBA
by lazulicurio on Saturday August 01, 2020 @06:41PM
(#24024537)
> 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.