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