Managing Views in SQL: Complete Guide

A view is a stored query that behaves like a table. You give it a name, and from then on you can query it exactly as if it were a real table — even though nothing is physically stored. Every time you select from a view, the underlying query runs again.

CREATE VIEW customer_contacts AS
SELECT customer_id, customer_name, region
FROM   customers;

After that statement, SELECT * FROM customer_contacts returns the same three columns as querying the customers table directly. The view runs the SELECT every time, and the result reflects whatever the customers table looks like right now.

Three reasons views are used:

• Simplification. A complex join with ten tables becomes a single name. Anyone writing a report queries the view instead of rebuilding the join.
• Security. A view can expose only the columns a team should see. The underlying table still has the sensitive columns, but they are invisible to the user who queries the view.
• Stability. If the underlying schema changes, you update the view. Every query that references the view keeps working, unchanged.

The trade-off is that views recompute their queries on every use. A simple view on a well-indexed table is nearly free. A view that joins ten tables and aggregates across them is expensive on every query — which is why materialized views exist as an alternative.

CREATE VIEW names a query. You write the SELECT you want to save, wrap it in the CREATE VIEW syntax, and give it a name. From that point on, the name refers to the saved query.

CREATE VIEW customer_orders AS
SELECT c.customer_name,
       o.order_id,
       o.order_amount,
       o.order_status
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

The view customer_orders now exists. Any query that references it gets the joined result. If someone later changes the join condition, they change it in the view definition, and every query that uses the view picks up the change automatically.

A good habit: build and test the SELECT as a standalone query first, then wrap it in CREATE VIEW. The view definition is just that query, so debugging it as a plain query before saving is easier than debugging through the view.

If you need to redefine the view — say the underlying table has changed — use CREATE OR REPLACE VIEW on databases that support it. That redefines the view in place without dropping and recreating, which matters on production systems where a brief window without the view would break downstream applications.

Each database exposes a view's structure and definition through its own system catalog or shortcut command.

-- Oracle, MySQL
DESCRIBE customer_orders;

-- PostgreSQL
\d customer_orders

-- MySQL, PostgreSQL — full definition
SHOW CREATE VIEW customer_orders;

-- Oracle
SELECT view_name, text FROM user_views WHERE view_name = 'CUSTOMER_ORDERS';

DESCRIBE gives you the column list — names, types, and nullability. It looks just like describing a table because from the client's perspective a view is a table.

SHOW CREATE VIEW gives you the full SQL that the view is defined by. This is the interesting one when you are debugging or trying to understand what a view actually does. It shows the joins, the filters, the aggregates — everything.

In a GUI client like SQL Server Management Studio or Oracle SQL Developer, you can also right-click a view in the schema browser and choose "View Definition" to see the same thing without writing the query.

When a view stops returning the right data, or a query against it is suddenly slow, the definition is the first place to look. It tells you exactly what the database is going to execute every time someone uses the view.

If a view is updatable, you write UPDATE, INSERT, or DELETE statements against it just as you would against a table. The database translates the change into an operation on the base table.

-- Simple view on a single table
CREATE VIEW customer_contacts AS
SELECT customer_id, customer_name, region, credit_limit
FROM   customers;

-- Update through the view
UPDATE customer_contacts
SET    credit_limit = 60000
WHERE  customer_id = 'C001';

The update sets Alice Corp's credit limit to 60,000 — in the underlying customers table. Anyone querying the view or the base table sees the new value.

What makes a view updatable

A view is updatable when it has a one-to-one mapping between its rows and the base table's rows. That generally means:

• Single base table — no joins
• No aggregate functions or GROUP BY
• No DISTINCT or HAVING
• No subqueries in the SELECT list

Break any of those, and the view is read-only. A view that joins customers to orders does not have a single obvious row to update when you change a column — the customer name appears on every order row, and updating one is meaningless.

INSTEAD OF triggers for complex views

CREATE TRIGGER trg_customer_orders_update
INSTEAD OF UPDATE ON customer_orders
FOR EACH ROW
BEGIN
    UPDATE customers
    SET    customer_name = :NEW.customer_name
    WHERE  customer_id = :NEW.customer_id;
END;

On Oracle, PostgreSQL, and SQL Server, you can attach an INSTEAD OF trigger that runs when someone tries to update the view. The trigger contains the logic to propagate the change to the right base tables. That gives you updatability for views that would otherwise be read-only, at the cost of writing the mapping logic yourself.

DROP VIEW removes the view definition from the database. The underlying tables and their data are untouched — only the saved query is gone.

DROP VIEW customer_orders;

After that statement, any query that references customer_orders fails with "object not found". Everything that used the view needs to be updated to reference a different name, or the view needs to be recreated.

The syntax is standard across databases. Oracle, PostgreSQL, MySQL, and SQL Server all use DROP VIEW view_name. Some databases have an optional CASCADE clause that also drops dependent objects — views that reference this view, or stored procedures that use it.

DROP VIEW customer_orders CASCADE;

When to use CASCADE and when not to. On a small schema where you know what depends on what, plain DROP VIEW is safer — it fails loudly if something depends on the view, and you can decide what to do. On a large schema where you are cleaning up an entire subtree of views, CASCADE saves a lot of time.

The cleaner approach in production: search for the view name across your codebase and database before dropping. If you find references, update them first. Then drop the view. That way you do not leave broken references behind, and you do not accidentally cascade away something you needed.

Managing views across multiple schemas is about centralisation and clarity. Without a strategy, views end up scattered — Sales has its own version of a customer view, Finance has another, and nobody is sure which one is authoritative.

Centralise shared views

CREATE SCHEMA reporting;

CREATE VIEW reporting.v_customer_summary AS
SELECT c.customer_id,
       c.customer_name,
       c.region,
       SUM(o.order_amount) AS total_spend
FROM   hr.customers c
JOIN   sales.orders  o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.region;

A dedicated reporting schema holds views that span domains. The view reads from hr and sales, qualifies every table explicitly, and lives in one predictable place.

Use a naming convention

Prefix every shared view with v_ or vw_. It makes views obvious in a list of objects and easy to search for. Some teams also use the domain in the name — v_customer_summary, v_monthly_revenue — so the name itself tells you what the view is for.

Grant access through roles

GRANT USAGE ON SCHEMA reporting TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst_role;

Grant access to the schema once instead of granting individual views. New views added later inherit the same access. That is far fewer grants to manage.

Review periodically

Views accumulate. Some are used constantly, others were created for a one-off analysis and never dropped. Every few months, check which views are being queried and drop the ones that are dead. Every unused view is a small maintenance obligation that adds up.

The general principle: put shared logic where everyone can find it, name things consistently, and clean up what is no longer needed. It is the same discipline you would apply to any other database object, but it matters more for views because they so easily multiply without anyone noticing.

WITH CHECK OPTION is a rule on updatable views. It says: any change made through the view must still satisfy the view's WHERE clause. Rows cannot be modified in a way that would move them out of the view's scope.

CREATE VIEW north_customers AS
SELECT customer_id, customer_name, region, credit_limit
FROM   customers
WHERE  region = 'North'
WITH CHECK OPTION;

That view shows only North-region customers. Without WITH CHECK OPTION, the following update would succeed and quietly remove Alice Corp from the view:

UPDATE north_customers SET region = 'South' WHERE customer_id = 'C001';

The base table would have Alice Corp in the South region. The view north_customers would no longer include her. Any query that relied on the view would silently lose her — no error, just missing data.

With WITH CHECK OPTION, the same UPDATE fails. The database refuses to make a change that would move a row out of the view's scope. The error message tells you what happened — the update violates the view's condition. Nothing is committed.

What WITH CHECK OPTION protects against

• Accidental updates that change a row's category and make it disappear from the view.
• INSERTs that would create rows outside the view's filter, invisible to anyone using the view.
• Data drift between what the view purports to show and what the base table actually contains.

What it does not protect against

WITH CHECK OPTION only applies to writes through the view. If someone has direct access to the base table, they can update the region without going through the view, and the row will leave the view's scope. That is why a view's security model usually includes revoking direct access to the base table.

Not every database supports WITH CHECK OPTION on every view. Views with complex joins, aggregates, or subqueries typically cannot have it. It is a rule for views that would be updatable anyway — it adds a constraint on top of the updatability, not in place of it.

Every view supports SELECT. Whether it supports INSERT, UPDATE, and DELETE depends on the view's shape.

SELECT — always allowed

SELECT * FROM customer_orders WHERE region = 'North';

Any query you can write against a table works against a view. Joins, filters, groupings, subqueries — all of them. The view is just a saved query that the outer query wraps around.

INSERT, UPDATE, DELETE — conditional

• Updatable views — simple views on a single base table. The database translates the write into an operation on the base table.
• Read-only views — complex views with joins, aggregates, or GROUP BY. The database cannot determine what the change should mean.
• Partially updatable — some views allow UPDATE and DELETE but not INSERT, or vice versa.

Checking whether a view is updatable

-- Oracle: check the updatable column
SELECT table_name, updatable
FROM   user_views
WHERE  view_name = 'CUSTOMER_ORDERS';

On databases that track this, the system catalog reports whether a view is updatable. If you cannot tell from the schema, the practical test is to try a small UPDATE on a test row and see what happens. If the view is read-only, the database rejects the write with a clear error.

INSTEAD OF triggers — the escape hatch

CREATE TRIGGER trg_customer_orders_delete
INSTEAD OF DELETE ON customer_orders
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE order_id = :OLD.order_id;
END;

On Oracle, PostgreSQL, and SQL Server, an INSTEAD OF trigger lets you define the behaviour for a write that the database would otherwise reject. The trigger runs instead of the default action and contains the logic you want. That effectively makes read-only views write-capable, as long as you write the trigger.

MySQL does not support INSTEAD OF triggers, so its read-only views really are read-only. That is worth knowing during migrations — a view that works on Oracle may need rewriting for MySQL.

A view is the natural place to encapsulate a complex query that gets reused. Instead of pasting the same ten-line join with aggregation into every report, you save it as a view and query the view.

CREATE VIEW customer_totals AS
SELECT c.customer_id,
       c.customer_name,
       c.region,
       COUNT(o.order_id)     AS order_count,
       SUM(o.order_amount)   AS total_spend,
       MAX(o.order_amount)   AS largest_order
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.region;

That view encapsulates a join, a filter, and three aggregate functions. Any report that needs per-customer totals queries the view — SELECT * FROM customer_totals WHERE total_spend > 20000 — without re-writing the aggregation logic.

The benefits of encapsulating complexity

• Consistency. Every query that uses the view gets the same calculation. No risk of one report using SUM and another using AVG by mistake.
• Maintainability. When the calculation changes — say the business decides to exclude cancelled orders — you change one view definition, and every report picks up the change.
• Onboarding. New developers do not need to understand the underlying join to use the view. They just query it like a table.

Where complexity in views goes wrong

A view that is too complex becomes a black box. If a view joins ten tables and aggregates four of them, debugging a report that uses it means understanding the whole view definition. Nested views — a view that queries another view — make this worse.

The general rule: one view should do one thing. A view that joins customers to orders does that one thing. A separate view that aggregates over the joined result does another. Keeping views focused makes them readable and makes debugging possible when something goes wrong.

-- View 1: the join
CREATE VIEW customer_orders AS
SELECT c.customer_id, c.customer_name, c.region, o.order_amount
FROM   customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- View 2: the aggregation over view 1
CREATE VIEW customer_totals AS
SELECT customer_id, customer_name, region,
       COUNT(order_amount) AS order_count,
       SUM(order_amount)   AS total_spend
FROM   customer_orders
GROUP BY customer_id, customer_name, region;

Each view is short, understandable on its own, and testable in isolation. Composed, they do what the single complex view did — but with far less cognitive load when something needs to change.

When a column is renamed or dropped, any view that referenced it breaks. The database does not automatically fix view definitions when the underlying schema changes — you have to update the view yourself.

-- Original view referencing order_date
CREATE VIEW recent_orders AS
SELECT order_id, order_date
FROM   orders
WHERE  order_date > '2024-01-01';

-- Column renamed to order_placed_date
-- The view now fails at query time

-- Update the view
CREATE OR REPLACE VIEW recent_orders AS
SELECT order_id, order_placed_date
FROM   orders
WHERE  order_placed_date > '2024-01-01';

CREATE OR REPLACE VIEW is the standard mechanism. It redefines the view in place without dropping and recreating — which matters because a brief window without the view would break any application currently using it.

When CREATE OR REPLACE is not available

Some databases or configurations do not support CREATE OR REPLACE VIEW on all view types. When that happens, the pattern is:

1. DROP VIEW recent_orders;
2. CREATE VIEW recent_orders AS ...

Between those two statements, the view does not exist. On a busy system, that window — often a few milliseconds — is a real concern. Schedule these updates during low-traffic periods, or wrap them in a transaction if the database supports transactional DDL.

Updating downstream dependencies

When a view's definition changes, anything that uses the view may need updating too. Other views that reference this one. Stored procedures. Application code. Reports. It is not enough to fix the view — the change cascades outward.

The practical approach: before any schema change, identify every view that could be affected. Update them in the right order — bottom-up, from the deepest views to the ones that reference them. Test each layer as you go. That way, when something breaks, you know which change caused it.

Views are the standard way to give someone access to part of a table without giving them access to all of it. You grant access to the view and revoke access to the underlying table.

Hiding sensitive columns

CREATE VIEW public_employee_data AS
SELECT employee_id, employee_name, department
FROM   employees;

GRANT SELECT ON public_employee_data TO analyst_role;
-- Do NOT grant access to the employees table

The employees table might have salary, social security number, and personal contact information. The public_employee_data view exposes only the columns anyone is allowed to see.

Because the analyst only has a grant on the view, they cannot bypass it to reach the underlying table. The security is enforced at the database layer, not in the application's UI.

Filtering rows by role

CREATE VIEW north_region_customers AS
SELECT customer_id, customer_name, region, credit_limit
FROM   customers
WHERE  region = 'North';

GRANT SELECT ON north_region_customers TO north_manager_role;

That view restricts rows as well as columns. The North manager sees every column for their region — and nothing about the other three regions. Any SQL they write is limited to what the view exposes.

Why this is stronger than application filtering

Application-level filtering is fragile. If a developer writes a query that forgets to add WHERE region = 'North', the North manager briefly sees the South's data. The database does not know what the application was supposed to do.

A view-backed security model is defensive. The North manager literally cannot query data outside the North region, because the only object they have access to is a view that filters by region. Application bugs cannot leak data that the database will not return.

Granting and revoking

-- Read-only access
GRANT SELECT ON public_employee_data TO analyst_role;

-- Read and write
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_directory TO manager_role;

-- Revoke when the team moves on
REVOKE ALL ON public_employee_data FROM analyst_role;

Grant through roles rather than individual users. Roles are easier to audit and easier to change — when someone leaves the team, you remove them from the role, and every grant comes off at once.

A view with a join presents the combined data from two or more tables as if it were one table. You write the join once in the view definition, and every query against the view sees the combined result.

CREATE VIEW customer_orders AS
SELECT c.customer_id,
       c.customer_name,
       c.region,
       o.order_id,
       o.order_amount,
       o.order_status
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Any query against the view sees the joined result. Someone who wants a customer with all their orders writes SELECT * FROM customer_orders WHERE customer_id = 'C001' and gets Alice Corp with her three orders — no join needed in their query.

Which join type to use

The join type matters, and it is easy to get wrong in a view. An INNER JOIN would exclude the three customers with no orders. Alice Corp, Charlie Ltd, Diana Retail would appear; Ethan Supplies, Fiona Mart, and George Wholesale would not.

A LEFT JOIN keeps every customer, filling the order columns with NULL where there are no orders. That is usually what you want for a customer-centric view — nobody expects a customer list to silently drop the customers who have not ordered.

The same reasoning applies in reverse. An order-centric view would want a LEFT JOIN on the customers side, so orders whose customer_id does not match a real customer still appear. On our data, every order has a valid customer, so the join behaves the same way — but if you later insert an order with a bad customer reference, the join type determines whether it shows up.

Multi-table joins in views

CREATE VIEW order_details AS
SELECT c.customer_name,
       o.order_id,
       o.order_amount,
       o.order_status,
       p.product_name,
       oi.quantity
FROM   customers    c  ON c.customer_id = o.customer_id
JOIN   orders       o  ON c.customer_id = o.customer_id
JOIN   order_items  oi ON o.order_id    = oi.order_id
JOIN   products     p  ON oi.product_id = p.product_id;

Views can chain joins just like regular queries. Each additional join adds more columns and potentially more rows — the same considerations that apply to a plain multi-table query apply here.

One risk with multi-table views: the row count can grow faster than you expect. If a customer has three orders and each order has four items, the customer appears twelve times in the joined view. Anyone who assumed "one row per customer" would get the wrong answer. Views should be named to reflect their grain — a view called customer_orders implies one row per order, not one row per customer.

Views with aggregate functions give you a saved summarization. Instead of writing SUM or AVG with GROUP BY every time you need per-group totals, you write it once in the view.

CREATE VIEW customer_totals AS
SELECT customer_id,
       COUNT(*)          AS order_count,
       SUM(order_amount) AS total_spend,
       AVG(order_amount) AS avg_order_value,
       MAX(order_amount) AS largest_order
FROM   orders
GROUP BY customer_id;

One row per customer, with four summary metrics. Any query that wants per-customer order statistics writes SELECT * FROM customer_totals and gets the aggregation already done.

On our data, Alice Corp has three orders, a total of 28,500, an average of 9,500, and a largest order of 12,000. Bob Traders has two orders, a total of 7,700, an average of 3,850, and a largest order of 4,500. Charlie Ltd and Diana Retail each have one order.

Grouping by more than one column

CREATE VIEW region_status_totals AS
SELECT c.region,
       o.order_status,
       COUNT(*)          AS order_count,
       SUM(o.order_amount) AS total_amount
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
GROUP BY c.region, o.order_status;

That view groups by two columns — one row per (region, status) pair. On our data, that is more rows than the single-column version because the North region with three statuses has separate rows for Delivered and Pending (there are no North Shipped orders).

What makes these views read-only

Any view that uses an aggregate function is not updatable. You cannot UPDATE a SUM(order_amount) because there is no single row in the base table that corresponds to the aggregate. The database refuses writes to these views.

That is fine for most uses — summary views are for reading. If someone needs to change a value, they update the base table directly, and the next query against the view picks up the change. The view is a read-only lens on live data.

Performance

Aggregated views are slow because they recompute the aggregation on every query. On a table with millions of orders, a query against an aggregated view runs the full GROUP BY every time.

Two mitigations. First, index the underlying table on the group-by columns — orders.customer_id here — so the database can walk the index instead of scanning the table. Second, if the view is used constantly and the data does not need to be up to the second, consider a materialized view that stores the aggregated result and refreshes on a schedule.

A view can do two things that a table cannot: aggregate data so the summary is pre-computed, and format data so it reads cleanly. Both are useful when you want to hand a friendly dataset to a non-technical audience.

Summarization

CREATE VIEW sales_summary AS
SELECT c.region,
       COUNT(DISTINCT c.customer_id) AS customers,
       COUNT(o.order_id)             AS orders,
       SUM(o.order_amount)           AS total_revenue,
       AVG(o.order_amount)           AS avg_order_value
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.region;

One row per region with five summary metrics. Any report that wants "regional performance at a glance" queries this view. On our data, North has three customers and five orders, South has two customers and two orders, West has two customers and one order.

Formatting

CREATE VIEW customer_directory AS
SELECT customer_id,
       UPPER(customer_name)               AS customer_name,
       COALESCE(region, 'Unassigned')     AS region,
       COALESCE(credit_limit, 0)          AS credit_limit,
       CASE
           WHEN credit_limit >= 75000 THEN 'Platinum'
           WHEN credit_limit >= 50000 THEN 'Gold'
           WHEN credit_limit >= 25000 THEN 'Silver'
           ELSE 'Standard'
       END                                AS tier
FROM   customers;

The view does four things at once. It uppercases the customer name for consistency. It replaces NULL regions with a descriptive label. It replaces NULL credit limits with 0. And it adds a computed tier column based on the credit limit.

Fiona Mart, whose credit limit is NULL, comes back with a limit of 0 and a tier of Standard. Charlie Ltd and George Wholesale come back as Platinum. The view presents a clean, labelled dataset without touching the underlying table.

Why this is often better than formatting in the UI

If the display rules live in the view, every consumer sees the same thing. A CSV export, a BI dashboard, and an ad-hoc query all get the same tier label for the same customer. If the rules lived in a JavaScript front-end, the CSV and the dashboard would drift from the UI as soon as one of them was updated.

Putting display logic in the view also makes it testable. You can query the view and see the labels, without spinning up the front-end. When the business changes the Platinum threshold, you update the view and check the result — no UI deployment needed.

GRANT gives a user or role permission on a view. REVOKE takes it away. You can grant SELECT, INSERT, UPDATE, DELETE, or any combination, just like on a table.

Granting access

-- Read-only
GRANT SELECT ON customer_directory TO analyst_role;

-- Read plus write
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_directory TO manager_role;

The analyst can query the view but not change anything. The manager can read and write — as long as the view is updatable. If the view is read-only, INSERT, UPDATE, and DELETE fail even with the grant in place.

Revoking access

REVOKE SELECT ON customer_directory FROM analyst_role;

REVOKE ALL ON customer_directory FROM former_employee;

Revoking is immediate. The next query the analyst tries against the view fails with a permissions error. No caching, no delay — the database enforces the change instantly.

Granting through roles is better

CREATE ROLE analyst_role;
GRANT SELECT ON customer_directory TO analyst_role;
GRANT analyst_role TO alice;
GRANT analyst_role TO bob;

You grant once, then attach users to the role. When a new analyst joins, you grant them the role — you do not edit every individual grant. When they leave, you revoke the role, and every permission comes off at once.

Role-based grants also make auditing easier. A single query against the database's grant tables shows you exactly what the analyst role has access to. If you had granted permissions to individual users, you would have to review each one separately.

Verifying grants

-- PostgreSQL
SELECT grantee, privilege_type
FROM   information_schema.role_table_grants
WHERE  table_name = 'customer_directory';

-- Oracle
SELECT grantee, privilege
FROM   user_tab_privs
WHERE  table_name = 'CUSTOMER_DIRECTORY';

Every database exposes its grant state through system views. Running these queries periodically — especially after team changes — confirms that only the right people have access. Stale grants are a security risk, and they build up faster than anyone expects.

The one thing to remember

Revoking a grant on a view does not revoke access to the underlying table. If the analyst also has a grant on the customers table, they can still read the columns the view was hiding. View-based security requires revoking the table grant separately, and it is easy to forget.

An indexed view stores the view's result set physically, like a table, so queries against it do not have to recompute the underlying query every time. It is the SQL Server equivalent of a materialized view.

The two-step process

-- Step 1: create the view with SCHEMABINDING
CREATE VIEW customer_totals_idx
WITH SCHEMABINDING AS
SELECT c.customer_id,
       c.customer_name,
       COUNT_BIG(*) AS order_count,
       SUM(o.order_amount) AS total_spend
FROM   dbo.customers c
JOIN   dbo.orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- Step 2: create a unique clustered index on the view
CREATE UNIQUE CLUSTERED INDEX idx_customer_totals
ON customer_totals_idx (customer_id);

The first statement creates the view with SCHEMABINDING. The second creates a unique clustered index, which stores the view's result physically. From that point on, queries against the view read the stored data instead of recomputing the aggregation.

What SCHEMABINDING does

SCHEMABINDING binds the view to the schema of the underlying tables. Once the view is bound, you cannot alter the referenced tables in ways that would break the view — you cannot drop a column the view uses, or change its type, without dropping the view first.

That is a real constraint, and it is why SCHEMABINDING exists. Without it, the index built on the view could become invalid if the underlying table changed. The binding is what makes the index safe.

Naming: COUNT_BIG vs COUNT

In the example above, I used COUNT_BIG(*) instead of COUNT(*). SQL Server requires COUNT_BIG in indexed views because COUNT returns an integer and could overflow on a large table. COUNT_BIG returns a bigint and avoids the problem. It is a small detail that trips people up the first time they try an indexed view with a count.

The cost of indexed views

Every write to the underlying tables now has to also maintain the index. An INSERT into orders is followed by an update to the indexed view. On a table with heavy write traffic, that doubles the write cost.

The trade-off is exactly the same as adding a regular index: reads get faster, writes get slower. Indexed views extend the trade-off to the whole result of a query, not just a single column. Use them when reads dominate and writes can tolerate the extra cost.

Support across databases

SQL Server has indexed views natively, as shown above. Oracle and PostgreSQL use materialized views instead — same concept, different mechanism. MySQL does not support materialized views or indexed views directly; the equivalent requires creating a summary table and refreshing it manually or via triggers.

A materialized view stores its result physically, so reads are fast. The cost is that the stored result can be stale — it only reflects the underlying tables as of the last refresh.

Creating a materialized view

-- Oracle
CREATE MATERIALIZED VIEW customer_totals_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT customer_id,
       COUNT(*)          AS order_count,
       SUM(order_amount) AS total_spend
FROM   orders
GROUP BY customer_id;

That creates a materialized view that refreshes on demand — you run DBMS_MVIEW.REFRESH('CUSTOMER_TOTALS_MV') or the equivalent when you want to update the stored result.

Refresh strategies

• On commit — the view refreshes immediately whenever the base tables change. Always fresh, but adds overhead to every write.
• On demand — the view refreshes when you tell it to, either manually or on a scheduler. Cheap writes, potentially stale reads.
• Complete refresh — recompute the whole result. Simple, but slow on large views.
• Fast refresh — apply only the changes since last refresh. Complex to configure, but very fast to run when it is set up.

Choosing a refresh strategy

For a report that runs hourly, an on-demand refresh every thirty minutes keeps the view reasonably fresh without hammering the source tables. For a dashboard that needs to be up to the second, on-commit is the only option — accept the write overhead and plan the schema accordingly.

Fast refresh is the ideal for large views. The view is refreshed incrementally, reading only the changed rows. Getting fast refresh to work requires the base tables to have materialized view logs — additional tables that track changes. The setup is fiddly, but once done, refreshes are cheap.

Indexing materialized views

CREATE INDEX idx_mv_customer ON customer_totals_mv (customer_id);

A materialized view is a physical table, so you can index it just like any other table. Indexing the columns that queries filter on makes the view even faster — the query finds the row in the index without scanning the stored result.

When materialized views are worth it

The decision is entirely about the read-write balance. A materialized view is worth it when a query is expensive, the data does not need to be up to the second, and the view is queried often enough that the storage cost is justified.

Dashboards that aggregate over millions of rows. Reports that run every morning on yesterday's data. Analytics that can tolerate a few hours of staleness. All of those are good candidates. A report that needs the latest order that was placed thirty seconds ago is not — the refresh would have to be on-commit, and by then you have lost the performance advantage.

Storage considerations

A materialized view takes up real space. On a table with a hundred million rows, a materialized view over the whole table is nearly as large as the table itself. If you have several materialized views on the same data, the storage can add up fast.

One strategy: use materialized views only over the subset of data that is frequently queried. A view that summarises the last year of activity is much smaller than one over the full history, and it covers most reporting needs.

This is a slightly confusing term to untangle, because CASCADE in the context of views does not mean the same thing as WITH CHECK OPTION. They are two different mechanisms.

What WITH CHECK OPTION actually does

As covered earlier, WITH CHECK OPTION restricts writes through a view to rows that still satisfy the view's WHERE clause. That is the mechanism that keeps a view "consistent" with its own definition — nothing written through the view leaves the view.

CREATE VIEW north_customers AS
SELECT customer_id, customer_name, region
FROM   customers
WHERE  region = 'North'
WITH CHECK OPTION;

That view stays consistent because no write through it can move a row out of the North region.

What CASCADE means for views

CASCADE appears in two contexts related to views. First, on DROP VIEW:

DROP VIEW customer_orders CASCADE;

That drops the view and any other objects that depend on it — other views, possibly stored procedures. Without CASCADE, the drop fails if anything depends on the view.

Second, CASCADE can appear in foreign key definitions and affects how changes propagate between parent and child tables. When a customer is deleted and the orders foreign key has ON DELETE CASCADE, the orders go too. Views that join these tables see the change immediately — the next query reflects the cascade.

How to keep a view consistent with its data

Three techniques, depending on what "consistent" means for your case:

• WITH CHECK OPTION — keep writes through the view inside the view's filter.
• Foreign key cascades — ensure that related tables do not get out of sync when a parent row is deleted or updated.
• Triggers — enforce custom consistency rules that neither of the above can express, in response to insert, update, or delete events.

The combination of these three covers most consistency scenarios. Use WITH CHECK OPTION for view-scoped writes, use foreign key cascades for relational integrity, and use triggers for anything that needs custom logic.

The point worth remembering

Views do not enforce any consistency by themselves. A view is just a saved query — it shows whatever the underlying tables contain at the moment you query it. All of the consistency mechanisms are separate features (CHECK OPTION, foreign keys, triggers) that keep the underlying data clean. The view simply reflects the current state.

If someone asks "how do you make a view consistent", the answer is usually: you make the underlying data consistent, and the view automatically reflects it.

NULL handling in views is about presentation. The underlying data has NULLs — that is a fact. The view is where you decide how those NULLs should appear to whoever queries the view.

Replacing NULLs with a display value

CREATE VIEW customer_limits AS
SELECT customer_id,
       customer_name,
       COALESCE(credit_limit, 0) AS credit_limit,
       COALESCE(region, 'Unassigned') AS region
FROM   customers;

Fiona Mart, whose credit limit is NULL, comes back with 0. Her region — the West region is not NULL for her, but if it were, it would come back as "Unassigned".

The choice of replacement value depends on meaning. Zero for numeric columns where "missing" and "no value" are effectively the same. A label like "Unassigned" or "Not Set" for text columns. A sentinel date for date columns.

Handling NULL in aggregations

CREATE VIEW customer_totals AS
SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id)                AS order_count,
       COALESCE(SUM(o.order_amount), 0) AS total_spend
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

The COALESCE around SUM turns the NULL that a LEFT JOIN produces for customers with no orders into 0. Fiona Mart, Ethan Supplies, and George Wholesale — the three customers with no orders — now show a total_spend of 0 instead of NULL.

The same does not apply to COUNT(o.order_id). A COUNT of an empty set is 0, not NULL, so it does not need wrapping. Knowing which aggregates produce 0 for empty sets (COUNT) and which produce NULL (SUM, AVG, MIN, MAX) is worth remembering.

Filtering NULL out of the view

CREATE VIEW customers_with_limits AS
SELECT customer_id, customer_name, credit_limit
FROM   customers
WHERE  credit_limit IS NOT NULL;

The opposite strategy. Sometimes the right answer is not to replace the NULL but to exclude the row entirely. A report that only cares about customers with an assigned limit can use this view and not worry about NULL rows at all.

Why this matters more than it seems

A NULL in a report is often displayed as a blank cell, while a 0 shows as 0. Two very different things to the reader. A missing value looks like an oversight; a zero looks like a fact. Deciding which interpretation is correct is a business decision, not a technical one — and the view is where you make that decision explicit.

One warning: once a view replaces NULLs with defaults, downstream queries cannot tell the difference. If a customer's credit limit is 0, is that because it really is 0, or because it was NULL and the view substituted 0? If the distinction matters, do not replace the NULL in the view — leave it visible and handle it in the consuming code. Views are convenient, but they can hide information you eventually need.

A view can calculate and expose business rules without changing the underlying data. The rule lives in the view definition, and every consumer sees the same answer.

A discount eligibility rule

CREATE VIEW discount_eligible AS
SELECT c.customer_id,
       c.customer_name,
       SUM(o.order_amount) AS total_purchases,
       CASE
           WHEN SUM(o.order_amount) > 500 THEN 'Eligible'
           ELSE 'Not Eligible'
       END AS discount_status
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Every customer in the view gets a discount status based on their total purchases. Any report, dashboard, or application that needs to know who is eligible for the discount queries this view and gets the answer directly.

On our data, every customer with orders would be Eligible because all their totals exceed 500. The rule is a bit contrived for this dataset, but the pattern is what matters — a business rule expressed once, in one place.

Why this is better than application-level logic

Imagine the discount rule is implemented in three places: a customer-facing website, an internal admin tool, and a monthly report. If the rule changes — the threshold moves from 500 to 1,000 — three codebases have to change. Miss one, and the customer-facing site and the report disagree about who gets a discount.

Put the rule in a view, and there is one place to change it. Every consumer reads from the view and gets the same answer. The threshold moves from 500 to 1,000 in one view definition, and every report automatically updates.

More complex rules

CREATE VIEW customer_pricing AS
SELECT c.customer_id,
       c.customer_name,
       c.region,
       c.credit_limit,
       CASE
           WHEN c.credit_limit >= 75000 AND c.region = 'North' THEN 0.15
           WHEN c.credit_limit >= 50000 THEN 0.10
           WHEN c.credit_limit >= 25000 THEN 0.05
           ELSE 0.00
       END AS discount_rate
FROM   customers c;

More elaborate rules work the same way. The view computes a discount rate based on credit limit and region. Every quote, every invoice, and every pricing tool can query the view to get the rate for a given customer.

The trade-off of putting business rules in views

Views are a convenient place for business rules, but not the only place. Some teams prefer to keep rules in application code where they can be unit-tested more naturally. Others keep them in the database so all consumers see the same result.

The choice depends on who consumes the data. If the rule only matters to one application, keeping it in that application's code is simpler. If the rule is used by many tools — a website, a BI dashboard, a report, an export — putting it in a view means one source of truth.

What matters most is picking one place and sticking to it. If the rule exists in the view and in application code and in a report template, they will eventually disagree, and the bug will be invisible until someone notices the numbers are different. Consistency beats cleverness here.