58
votes
Accepted
How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?
You can't. The feature is disabled in 2017 RTM.
That said, you can...
Using AdventureWorks:
CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product ...
- 90.2k
44
votes
Query the definition of a materialized view in Postgres
Looks like 9.3 and up you can do:
select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';
More info found here: https://stackoverflow.com/questions/29297296/postgres-...
- 541
27
votes
Accepted
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
You can do this with a function that runs in the security context of its owner.
Function that refreshes the view (create it with the user that owns the MV/table):
CREATE OR REPLACE FUNCTION ...
- 31.9k
27
votes
Accepted
Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?
CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on ...
- 1,591
21
votes
ALTER VIEW drops Index from View
There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:
...
- 28.5k
18
votes
Accepted
What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?
As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "...
- 26.9k
16
votes
Accepted
How to do an incremental refresh of a PostgreSQL Materialized View?
There is no INCREMENTAL, it's very simple.. From the docs
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]
Perhaps you're confusing it with CONCURRENTLY which is about ...
- 64.6k
15
votes
Replace a materialized view in Postgres
For my situation, I prefer to limit the drops by using a view layer:
Create a copy of the materialized view suffixed with "_new" and also use "WITH NO DATA" for performance, make sure any indexes are ...
- 253
14
votes
Refresh a PostgreSQL materialized view automatically without using triggers
As a_horse_with_no_name said in a comment:
No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – ...
- 64.6k
14
votes
Accepted
Why SQL does not use the indexed view?
Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins.
It may later chose to match part(s) of, or the whole plan, back ...
- 90.2k
14
votes
Accepted
Inability to query underlying table of a schema-bound view
From the documentation:
A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns ...
- 90.2k
14
votes
Accepted
Indexed view not used in actual execution plan?
You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server) in order for your query to use the indexed view (instead of expanding it to the underlying table). This ...
- 39.5k
12
votes
Trigger to update a materialized view once after relevant transaction?
An OK solution using triggers
This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.
(In the ...
- 945
12
votes
Accepted
Analyze needed after a refresh materialized view?
Table statistics are not updated automatically.
They remain unchanged if refreshed with the CONCURRENTLY option.
Without CONCURRENTLY, a new file is written for the table, and the basic counts ...
- 182k
12
votes
Accepted
Materialized views Performance
The data of a MV is stored in a regular table, there is no magic to that. But access is typically (much) faster for multiple possible reasons:
multiple tables joined already
smaller row size with ...
- 182k
11
votes
Accepted
Create Nested XML in a Sql Server Indexed (Materialized) View
Short answer: You can't.
Long answer:
There's a few things going on here.
First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". ...
- 1,953
11
votes
Accepted
Postgres Materialized View Dramatically Increasing In Size
My issue was running a "refresh materialized view concurrently" before the autovacuum had finished running, leading to table bloat. Now I manually vacuum analyze after every refresh.
- 181
11
votes
Accepted
SQL Server - Is there a way to prevent shared range locks to 'infinity' when using an indexed view?
There's nothing you can do about this. SQL Server automatically takes steps to ensure the indexed view always stays synchronized with the base tables.
When reading the indexed view to see if the data ...
- 90.2k
11
votes
Accepted
ALTER VIEW drops Index from View
You can prevent altering an indexed view with a DDL trigger. But the implementation is a bit complex, because the DDL trigger runs after the view has been altered and the index dropped, but before ...
- 47.6k
10
votes
Accepted
How does SQL Server update indexed views?
SQL Server does not completely rebuild the view after every change. That would be grossly inefficient. Instead, indexed views are maintained incrementally using delta algebra. The query processor ...
- 90.2k
9
votes
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
@Philᵀᴹ's second function attempt is vulnerable to SQL injection by using:
SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW ...
- 191
8
votes
Export Materialized View from postgresql as a table?
I don't think there is a single-step way to do this. Are you trying to do this with one MV, or with a whole database full of them?
To get the proper CREATE TABLE statement with all the column types, ...
- 41.3k
8
votes
Accepted
Indexed view - Cannot create index because "select list of the view contains an expression on result of aggregate function or grouping column."
Well, the error message is technically correct:
because the select list of the view contains an expression on [...] grouping column
The expression is your function, which takes Address as a ...
- 90.2k
7
votes
Accepted
Why does the READPAST hint cause indexed views to be ignored?
Reusing the example table and indexed view from my article Another Reason to Use NOEXPAND hints in Enterprise Edition:
CREATE TABLE dbo.T
(
col1 integer NOT NULL
);
GO
INSERT dbo.T WITH (TABLOCKX)...
- 90.2k
7
votes
Updating an indexed view with NOEXPAND
This works as you want on Enterprise/Developer Edition:
use tempdb
go
CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
...
- 47.6k
7
votes
Accepted
Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none?
Naming conventions are largely a question of taste and style.
The one important thing is to stay consistent. An inconsistent naming convention is often worse than none at all. To give my opinion: don'...
- 182k
7
votes
Accepted
Is there a way to disable logging / backups for view indexes?
nope
There has been a long-standing request for SQL Server to support unlogged objects. It is available in varying forms in other database systems, both for tables and for materialized views.
However, ...
6
votes
Accepted
Why would Indexed View perform much worse than equivalent table?
Indexed views often can't be used as drop-in replacements for tables. The reason is that the WITH (NOEXPAND) hint is often needed. Often, the view is expanded (despite the index) and the underlying ...
- 760
6
votes
Trigger to update a materialized view once after relevant transaction?
Well, likely you're doing it wrong. While the verb REFRESH may connote otherwise, you're actually rewriting the table when you run it. That kind of workload is heavy to run in a trigger (though you ...
- 64.6k
6
votes
Accepted
Can't create indexed view against table with masked columns
As far as I know, this is not supported, and you can only use tables in this case.
As found in following article which is using Row level security which is not the same as Dynamic Data masking, but ...
- 1,772
Only top scored, non community-wiki answers of a minimum length are eligible