Views vs Materialized Views in SQL

Posted by Kyle Hankinson March 2, 2023

---

Views and materialized views both provide a named query you can use like a table, but they work very differently under the hood.

Regular Views

A view is a saved query. It stores no data — every time you query the view, the underlying query runs:

CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';

-- This runs the full query every time
SELECT * FROM active_customers;

Pros: Always up-to-date, no storage cost, simple to create. Cons: Can be slow if the underlying query is complex.

Materialized Views

A materialized view stores the query results physically. It is a cached snapshot that must be refreshed to pick up changes:

-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    date_trunc('month', order_date) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1;

-- Query is instant — reads from stored data
SELECT * FROM monthly_sales;

Pros: Fast reads (pre-computed data), great for expensive aggregations. Cons: Stale data between refreshes, uses disk space.

Refreshing Materialized Views

PostgreSQL

-- Full refresh (locks the view during refresh)
REFRESH MATERIALIZED VIEW monthly_sales;

-- Concurrent refresh (no lock, requires a unique index)
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Oracle

-- Manual refresh
BEGIN
    DBMS_MVIEW.REFRESH('monthly_sales');
END;

-- Auto-refresh on commit
CREATE MATERIALIZED VIEW monthly_sales
REFRESH ON COMMIT AS
SELECT ...;

-- Auto-refresh on a schedule
CREATE MATERIALIZED VIEW monthly_sales
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/24 AS
SELECT ...;

Oracle's materialized views are the most mature — they support automatic refresh on commit, scheduled refresh, and incremental (fast) refresh using materialized view logs.

SQL Server: Indexed Views

SQL Server does not have materialized views by name. Instead, it uses indexed views — a view with a unique clustered index that physically stores the data:

CREATE VIEW dbo.monthly_sales WITH SCHEMABINDING AS
SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    SUM(total) AS revenue,
    COUNT_BIG(*) AS order_count
FROM dbo.orders
GROUP BY YEAR(order_date), MONTH(order_date);

CREATE UNIQUE CLUSTERED INDEX idx_monthly_sales
ON dbo.monthly_sales (order_year, order_month);

SQL Server indexed views update automatically when the underlying data changes — no manual refresh needed. But they have restrictions: SCHEMABINDING is required, and only certain query patterns are supported.

When to Use Each

Scenario	Use
Simple query abstraction	Regular view
Security (restrict column access)	Regular view
Expensive aggregation query	Materialized view
Dashboard or reporting queries	Materialized view
Data that must be real-time	Regular view
Data that can be minutes/hours old	Materialized view

MySQL

MySQL does not support materialized views natively. The workaround is to create a regular table and populate it on a schedule:

-- Poor man's materialized view
CREATE TABLE monthly_sales_cache AS
SELECT ... FROM orders GROUP BY ...;

-- Refresh via scheduled event
CREATE EVENT refresh_monthly_sales
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    TRUNCATE TABLE monthly_sales_cache;
    INSERT INTO monthly_sales_cache SELECT ... FROM orders GROUP BY ...;
END;

---