SQL Server Views Explained: Standard, Indexed, and Updatable Views

Chapter 1 (Fundamentals) previewed views as a saved, reusable SELECT. SQL Server actually offers three genuinely different flavors, each with different rules — and one of them (indexed views) is a real physical storage decision, not just a query-organization convenience.

Standard View

CREATE VIEW dbo.vw_WestRegionSales AS
SELECT sale_id, salesperson, amount, sale_date FROM dbo.Sale WHERE region = 'West';
GO

SELECT * FROM dbo.vw_WestRegionSales WHERE amount > 3000;

Always reflects live data — it’s just a saved query, re-executed each time it’s referenced. A standard view has zero storage cost of its own; think of it as a named, reusable shortcut for a SELECT, nothing more.

Updatable Views

UPDATE dbo.vw_WestRegionSales SET amount = 4300 WHERE sale_id = 1;

A simple, single-table view can accept INSERT/UPDATE directly — SQL Server translates the update against the view back into an update against the underlying Sale table automatically. But it becomes non-updatable the moment it involves a JOIN, GROUP BY/aggregate, DISTINCT, or UNION. SQL Server can no longer unambiguously map an update back to a single row in a single base table.

-- Confirm this yourself: a JOIN-based view rejects UPDATE outright
CREATE VIEW dbo.vw_SaleWithRegionName AS
SELECT s.sale_id, s.amount, r.region_name FROM dbo.Sale s JOIN dbo.Region r ON r.region_id = s.region_id;
GO
UPDATE dbo.vw_SaleWithRegionName SET amount = 5000 WHERE sale_id = 1;
-- Msg 4405: View or function ... is not updatable because the modification affects
-- multiple base tables.

Indexed (Materialized) Views

CREATE VIEW dbo.vw_RegionTotals
WITH SCHEMABINDING
AS
SELECT region, SUM(amount) AS total_sales, COUNT_BIG(*) AS sale_count
FROM dbo.Sale
GROUP BY region;
GO

CREATE UNIQUE CLUSTERED INDEX IX_RegionTotals ON dbo.vw_RegionTotals (region);

Once indexed, SQL Server physically stores and maintains the aggregate automatically as data changes — genuinely useful for expensive aggregates queried extremely often (a dashboard hit by hundreds of requests per minute), at the cost of slightly slower writes to the base table, since every INSERT/UPDATE/DELETE to Sale now also has to update the materialized total. This is a real space-vs-time tradeoff, the same category of decision you’ll formalize fully once indexing is covered in Chapter 8.