Lesson 13 of 22Intermediate15 min

Views

By the end of this lesson

Name a query for reuse, and understand what a view does not do.

A view is a saved query with a name. Once it exists, you select from it as though it were a table.

That is the whole feature, and it is genuinely useful: a join you write weekly becomes a name, and the definition lives in one place instead of being copied into six reports.

Creating and using a view

SQL

CREATE VIEW vw_order_summary AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    c.customer_id,
    c.company_name,
    c.country,
    SUM(oi.quantity * oi.unit_price) AS goods_value,
    o.shipping_fee,
    SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_total
FROM orders AS o
JOIN customers   AS c  ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY
    o.order_id, o.order_date, o.status,
    c.customer_id, c.company_name, c.country, o.shipping_fee;

-- Now use it like a table
SELECT company_name, order_total
FROM vw_order_summary
WHERE country = 'India'
  AND order_date >= '2026-01-01'
ORDER BY order_total DESC;

The view definition contains the join and the grouping once. Every query against it inherits both, correctly.
Because the grouping collapses order_items to one row per order, shipping_fee is not multiplied. Encoding that correctly in a view is a real benefit — six people writing the query independently will not all get it right.
The vw_ prefix is a convention, not a requirement. Some naming rule that distinguishes views from tables saves confusion when a query is slow and you are working out what it touches.

Reading the previous callout, some people conclude a view is pointless. That is the wrong conclusion. A view removes duplicated query logic, which removes the risk of six slightly different versions of "order total" being in circulation. What it does not do is change performance.

What views are genuinely good for

Four reasons to create one:

One definition of a calculation: "Order total" means goods plus shipping, excluding cancelled orders. Define it once in a view and every report agrees.
Hiding complexity: A report author can query vw_order_summary without knowing the four-table join or the row multiplication trap underneath it.
Restricting columns: Grant access to a view that excludes salary, rather than to the employees table. Permissions on a view are covered in the security lesson.
A stable surface over a changing schema: Split a table in two and you can keep a view with the old shape, so existing queries continue to work while callers migrate. This buys time; it does not remove the need to migrate.

Materialised views: the exception that does store results

If you want a stored result, that is a different feature with a different name and different costs:

	Ordinary view	Materialised / indexed view
What is stored	The query text only	The query text and the computed result
Freshness	Always current, because it runs on read	SQL Server indexed views are maintained automatically; PostgreSQL materialised views need an explicit REFRESH
Read cost	The full underlying query, every time	Reading stored rows, which can be dramatically cheaper
Write cost	None	Real — writes to base tables must update the stored result
Restrictions	Few	Many. SQL Server indexed views require schema binding, deterministic expressions, and disallow constructs including outer joins and most subqueries

The restrictions are the reason indexed views are less common than they sound. The view above would not qualify in SQL Server as written, because an indexed aggregate view has to include COUNT_BIG(*) and meet several other conditions.

Treat them as a targeted fix for a specific measured problem — an expensive aggregation read far more often than its base tables change — rather than a general technique.

Can you write through a view?

Sometimes. A view over a single table, with no grouping, no DISTINCT and no aggregates, is generally updatable: an UPDATE against it updates the underlying table.

A view with a join or an aggregate is not, because the database cannot tell which base rows your change refers to. vw_order_summary above has both, so it is read-only.

Writing through views is possible and rarely worth the ambiguity. Treat views as a read surface and write to tables directly, or through a stored procedure.

Common mistake

Common mistakes

Creating a view expecting it to cache. The query underneath runs on every read, so a slow query wrapped in a view is still slow. Fix the query or the indexes.

Building views on views on views. Each layer is expanded into the final query, and four layers deep it becomes very hard to tell what is actually being read or why the plan looks the way it does. Two layers is usually the practical limit.

Using SELECT * in a view definition. SQL Server resolves the columns when the view is created, so adding a column to the base table does not add it to the view — and the view can end up reporting stale column metadata until it is refreshed. Name the columns.

Assuming a view restricts what a user can see. It only does if the underlying table permissions are removed. A user with SELECT on employees can query it directly and bypass the view entirely.

Summary

A view is a named query — the definition is stored, the result is not
Selecting from a view runs the underlying query every time, so it changes nothing about performance
Views remove duplicated query logic, hide complexity, and can restrict which columns are exposed
Materialised and indexed views do store results, at a write cost and with significant restrictions
Views with joins or aggregates are read-only, because a view row maps to many base rows

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

Create a view called vw_active_products that shows product name, category, unit price and stock level for products that are not discontinued and have stock available. Then write one query against it.

Show solution

A single-table view with a WHERE clause and no aggregate. The value is that "active product" is now defined in one place, so nobody has to remember that it means both not discontinued and in stock.

This view is technically updatable, because it selects from one table with no grouping. That is worth knowing and not worth relying on — an UPDATE that changes discontinued to 1 would make the row vanish from the view, which surprises people.

Note what the view does not do: it does not make the read faster. If this query were slow, the fix would be an index on the filtered columns, not a view.

SQL

CREATE VIEW vw_active_products AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.unit_price,
    p.units_in_stock
FROM products AS p
WHERE p.discontinued = 0
  AND p.units_in_stock > 0;

-- Using it
SELECT category, COUNT(*) AS available, AVG(unit_price) AS average_price
FROM vw_active_products
GROUP BY category
ORDER BY available DESC;

Think about it

A colleague says a dashboard is slow, so they will "put the query in a view to speed it up". What do you tell them, and what would you suggest instead?

Show solution

The view will run exactly the same query, so the dashboard will be exactly as slow. The misconception is understandable — the word "view" sounds like something pre-built.

The useful next steps are diagnostic: look at the execution plan to find which part is expensive, check whether the filtered and joined columns are indexed, and check whether the query is asking for more rows or columns than the dashboard displays.

If the query is genuinely expensive and the data changes far less often than it is read, then a stored result is the right idea — a materialised or indexed view, or a summary table refreshed on a schedule. That is a real design decision with a real cost, which is different from expecting a plain view to solve it.

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

What happens when you SELECT from an ordinary view?

The database returns rows saved when the view was createdThe database runs the view's underlying query against current dataThe database returns rows from the last time anyone queried the viewThe database refreshes the view and then reads the stored copy

Why is a view with a GROUP BY generally not updatable?

Views are always read-only by designBecause one view row represents many base rows, so the database cannot tell which rows to changeBecause aggregates are stored separately from tablesBecause GROUP BY requires an index before writes are allowed

Saved in this browser only.