Can my DB?

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT product_id, SUM(amount) AS total
  FROM sales
  GROUP BY product_id;

-- Manual refresh (exclusive lock)
REFRESH MATERIALIZED VIEW sales_summary;

-- Non-blocking refresh (requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

• Added in 9.3. No automatic refresh; drive REFRESH MATERIALIZED VIEW from an external scheduler (pg_cron, cron, your job runner).
• WITH NO DATA creates the view empty; the first REFRESH populates it.

• Not supported. The typical substitute is a regular table driven by the EVENT scheduler or application code; FlexViews offers third-party trigger-based maintenance.

• Not supported. Same table-plus-scheduler substitute as MySQL. MariaDB ColumnStore (a separate analytical engine) has its own materialized-view concept.

CREATE MATERIALIZED VIEW sales_summary
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  ENABLE QUERY REWRITE
AS
  SELECT product_id, SUM(amount) AS total
  FROM sales
  GROUP BY product_id;

• Refresh modes: FAST (incremental, needs materialized view logs on the base tables), COMPLETE (full), FORCE (FAST if possible, else COMPLETE).
• Refresh timing: ON COMMIT (at every committing DML) or ON DEMAND (manual).
• ENABLE QUERY REWRITE lets the optimizer redirect queries against the base tables to the materialized view.

CREATE VIEW sales_summary
WITH SCHEMABINDING
AS
  SELECT product_id, SUM(amount) AS total, COUNT_BIG(*) AS cnt
  FROM dbo.sales
  GROUP BY product_id;

CREATE UNIQUE CLUSTERED INDEX idx_sales_summary
  ON sales_summary (product_id);

• Called indexed views. Maintained synchronously by the engine on every DML; no REFRESH command.
• Strict definition rules: WITH SCHEMABINDING, two-part table names, no subqueries, CTEs, outer joins, DISTINCT, TOP, UNION, derived tables, or non-deterministic functions.
• A unique clustered index has to be built on the view before any non-clustered indexes.

• Not supported. A regular table populated with INSERT INTO ... SELECT and rebuilt on demand is the typical stand-in.

CREATE MATERIALIZED VIEW sales_summary
AUTO REFRESH YES
AS
  SELECT product_id, SUM(amount) AS total
  FROM sales
  GROUP BY product_id;

• AUTO REFRESH YES enables scheduler-driven refresh; REFRESH MATERIALIZED VIEW triggers it manually.
• Definition restrictions: no ORDER BY / LIMIT / OFFSET, no standard views, UDFs, or external tables.
• Incremental refresh is only available for definitions without aggregations; otherwise each refresh is a rebuild.

CREATE TABLE sales_summary AS (
  SELECT product_id, SUM(amount) AS total
  FROM sales
  GROUP BY product_id
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE sales_summary;

• Called Materialized Query Tables (MQTs). REFRESH DEFERRED is manual (REFRESH TABLE); REFRESH IMMEDIATE is synchronous on write.
• With ENABLE QUERY OPTIMIZATION, the optimizer can redirect queries against the base tables to an MQT.

-- Materialized View (Enterprise Edition required)
CREATE MATERIALIZED VIEW sales_summary AS
  SELECT product_id, SUM(amount) AS total
  FROM sales
  GROUP BY product_id;

-- Dynamic Table (more flexible alternative, supports joins)
CREATE DYNAMIC TABLE sales_summary
  TARGET_LAG = '1 minute'
  WAREHOUSE = my_wh
  AS SELECT product_id, SUM(amount) AS total
     FROM sales GROUP BY product_id;

• Enterprise Edition only. Maintained by a background service; no REFRESH statement.
• Single-table definitions only. No joins, HAVING, ORDER BY, window functions, or UDFs; aggregates limited to SUM, COUNT, AVG, MIN, MAX, STDDEV.
• Dynamic Tables are available on all tiers and are the more capable option: joins, incremental refresh, configurable TARGET_LAG.

CREATE MATERIALIZED VIEW mydataset.sales_summary
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS
  SELECT product_id, SUM(amount) AS total
  FROM mydataset.sales
  GROUP BY product_id;

• Incremental refresh runs in the background when base tables change; refresh_interval_minutes (default 30) controls cadence.
• Can be partitioned and clustered. The optimizer can redirect base-table queries to the MV transparently.