Materialized views vs Normal views

Question

What’s the difference between materialized views and normal views in SQL?

Tuhin Paul · Answer

Part -2 SQL code that creates both a normal view and a materialized view for active policies -- Create a normal (virtual) view for active policies with joined customer, vehicle, and claim details. CREATE VIEW vw_active_policies AS SELECT c.CustomerID, c.Name AS CustomerName, c.Email AS CustomerEmail, p.PolicyID, p.PremiumAmount, p.StartDate, p.EndDate, v.VIN, v.Make, v.Model, v.Year, cl.ClaimID, cl.ClaimStatus, cl.ClaimAmount, cl.ClaimDate FROM CustomerDB.dbo.Customers AS c JOIN PolicyDB.dbo.Policies AS p ON c.CustomerID = p.CustomerID JOIN VehicleDB.dbo.Vehicles AS v ON p.VehicleID = v.VehicleID LEFT JOIN ClaimsDB.dbo.Claims AS cl ON p.PolicyID = cl.PolicyID WHERE p.EndDate >= GETDATE(); -- Only active policies -- Create a materialized view for active policies. -- (Note: The exact syntax for materialized views may vary by RDBMS. The following is an example.) CREATE MATERIALIZED VIEW mv_active_policies AS SELECT c.CustomerID, c.Name AS CustomerName, c.Email AS CustomerEmail, p.PolicyID, p.PremiumAmount, p.StartDate, p.EndDate, v.VIN, v.Make, v.Model, v.Year, cl.ClaimID, cl.ClaimStatus, cl.ClaimAmount, cl.ClaimDate FROM CustomerDB.dbo.Customers AS c JOIN PolicyDB.dbo.Policies AS p ON c.CustomerID = p.CustomerID JOIN VehicleDB.dbo.Vehicles AS v ON p.VehicleID = v.VehicleID LEFT JOIN ClaimsDB.dbo.Claims AS cl ON p.PolicyID = cl.PolicyID WHERE p.EndDate >= GETDATE() WITH DATA; -- Precompute and store the result

Tuhin Paul · Answer

Part - 1 Normal (Virtual) Views: Definition: A normal view is a virtual table that is defined by a SQL query. It does not store data physically; rather, it dynamically generates results when the view is queried. Characteristics: On-Demand Computation: Data is computed in real-time each time the view is accessed. Up-to-Date: Always reflects the current state of the underlying base tables. Performance: Can be slower for complex queries since the computation occurs at query time. Storage: Minimal storage requirements as only the SQL query definition is stored. Materialized Views: Definition: A materialized view is similar to a normal view but stores the precomputed result set physically on disk. It is refreshed periodically or on-demand. Characteristics: Precomputed Data: Offers fast query performance since the data is already computed and stored. Staleness: May not reflect the most recent changes in the underlying data until it is refreshed. Performance: Ideal for complex aggregations and joins where real-time computation can be resource intensive. Storage: Requires additional disk space to store the computed data. Refresh Strategies: Can be refreshed automatically (e.g., on a schedule) or manually.

Shubham Sidnale · Answer

The key difference between materialized views and normal views in SQL lies in how they store and retrieve data: 1. Normal Views A view is a virtual table that is based on a SQL query. It does not store data physically; instead, it dynamically fetches data from the underlying tables whenever it is queried. It always retrieves the latest data from the base tables. Performance Impact : Since the data is fetched in real-time, complex views with multiple joins and aggregations can slow down query execution. Example : CREATE VIEW EmployeeView AS SELECT EmployeeID, Name, Department FROM Employees; 2. Materialized Views A materialized view is a physical copy of a query result that is stored in the database. Unlike normal views, it does not update automatically when the base table changes. It needs to be refreshed manually or on a schedule . It improves query performance because data retrieval is faster (since it’s precomputed and stored). Storage Cost : Since data is stored physically, it consumes additional storage space. Example (PostgreSQL/Oracle): CREATE MATERIALIZED VIEW SalesSummary AS SELECT CategoryID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY CategoryID; Refreshing Data : REFRESH MATERIALIZED VIEW SalesSummary; When to Use Which? Feature Normal View Materialized View Storage No storage (virtual) Stored physically Data Freshness Always up-to-date Stale until refreshed Performance Slower for complex queries Faster for read-heavy operations Use Case Frequently updated data Precomputed aggregates, reporting

Muhammad Imran Ansari · Answer

Normal Views (Standard Views): A normal view is a virtual table based on a SELECT query. It does not store data but dynamically retrieves it when queried. No physical storage, slower compared to materialized views and always shows up-to-date data from the base tables. -- Example CREATE VIEW ActiveUsers AS SELECT UserID, UserName FROM Users WHERE IsActive = 1; Materialized Views: A materialized view stores the result of the query physically in the database. Requires disk space since it stores the computed data, faster for read-heavy workloads and needs to be refreshed (manually or automatically) to reflect changes in base tables. -- Example: CREATE MATERIALIZED VIEW SalesSummary AS SELECT Region, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY Region; -- To refresh it, you might run: REFRESH MATERIALIZED VIEW SalesSummary; Key Differences Summary: Feature Normal View Materialized View Data Storage No (Virtual Table) Yes (Stored Data) Query Performance Slower (Executes query each time) Faster (Precomputed results) Always Up-to-date? Yes No (Needs refresh) Use Case Frequently changing data Expensive queries, reporting, caching Refresh Mechanism Not needed Manual or automatic refresh

Materialized views vs Normal views

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (4)