Views Interview Questions & Answers

A view is a named, stored SELECT statement. Querying a view runs the underlying query at that moment — the result is not stored (contrast with a materialized view). Views look and act like tables from the caller's perspective but contain no data of their own.

-- Define the view
CREATE VIEW active_users AS
  SELECT id, name, email
  FROM   users
  WHERE  deleted_at IS NULL;

-- Query it exactly like a table
SELECT * FROM active_users WHERE name ILIKE 'alice%';

-- The database executes the underlying query at query time:
-- SELECT id, name, email FROM users
-- WHERE deleted_at IS NULL AND name ILIKE 'alice%'

Rule of thumb: use views to give a stable, simple name to a complex or frequently repeated query — and to hide columns or rows that callers should not access.

-- Create (fails if the view already exists)
CREATE VIEW recent_orders AS
  SELECT id, customer_id, total, created_at
  FROM   orders
  WHERE  created_at >= now() - INTERVAL '30 days';

-- Replace (redefines in place — dependent GRANTs are preserved in Postgres)
CREATE OR REPLACE VIEW recent_orders AS
  SELECT id, customer_id, total, created_at, status
  FROM   orders
  WHERE  created_at >= now() - INTERVAL '30 days';

-- Drop
DROP VIEW recent_orders;

-- Drop even if other views depend on it (Postgres)
DROP VIEW recent_orders CASCADE;

CREATE OR REPLACE in Postgres requires the new definition to have the same columns in the same order (you can add columns at the end, but not remove or reorder existing ones). SQL Server has no OR REPLACE; use ALTER VIEW instead.

Rule of thumb: use CREATE OR REPLACE VIEW in migrations to avoid dropping dependent objects. Only use DROP VIEW when removing the view entirely.

1. Simplify complex queries — encapsulate multi-table joins so callers write SELECT * FROM order_summary instead of a 10-line join.
2. Row and column security — expose only the columns and rows a role should see, without granting access to the base tables.
3. Stable interface over a changing schema — rename or restructure tables while keeping the view's column names unchanged for backward compatibility.
4. Soft-delete / active-record filter — WHERE deleted_at IS NULL applied once in the view, not in every query.

-- Security: analysts can see revenue but not PII
CREATE VIEW sales_summary AS
  SELECT date_trunc('day', created_at) AS day,
         SUM(total)                   AS revenue,
         COUNT(*)                     AS order_count
  FROM   orders
  GROUP  BY 1;

GRANT SELECT ON sales_summary TO analyst_role;
-- analysts cannot SELECT from the orders table directly

Rule of thumb: a view is the right tool when multiple queries share the same complex join or filter logic — it is a DRY principle applied to SQL.

Yes — a view is updatable if it satisfies all of these conditions:

• Maps to exactly one base table.
• Does not use DISTINCT, GROUP BY, HAVING, aggregates, window functions, UNION, or set operations.
• Does not use subqueries in the SELECT list.

CREATE VIEW active_products AS
  SELECT id, name, price
  FROM   products
  WHERE  archived = FALSE;

-- This INSERT goes through to the products table
INSERT INTO active_products (name, price) VALUES ('Widget', 9.99);

-- This UPDATE modifies the underlying products row
UPDATE active_products SET price = 8.99 WHERE id = 1;

-- Caveat: you could insert a row that then disappears from the view
-- if archived is not set to FALSE by default. Use WITH CHECK OPTION to prevent this.

Rule of thumb: rely on updatable views only for simple single-table views with a clear filter. For complex views, use INSTEAD OF triggers or handle mutations in application code on the base table.

WITH CHECK OPTION prevents INSERT and UPDATE through the view from producing rows that would no longer be visible through that view. Without it, you can insert a row that immediately "disappears" from the view.

CREATE VIEW active_products AS
  SELECT id, name, price, archived
  FROM   products
  WHERE  archived = FALSE
WITH CHECK OPTION;

-- This fails: the new row would have archived = TRUE,
-- so it would not be visible through the view
UPDATE active_products
SET    archived = TRUE
WHERE  id = 1;
-- ERROR: new row violates check option for view "active_products"

-- Without WITH CHECK OPTION the UPDATE would succeed and the row
-- would silently vanish from the view's result set.

Rule of thumb: add WITH CHECK OPTION to every updatable view that filters rows — it prevents mutations that produce invisible rows and makes the view behave as a consistent, self-contained interface.

	View	CTE
Scope	Persistent, reusable across sessions	Single-query, local
Access control	Can GRANT SELECT on it	Cannot grant independently
Performance	Optimizer can inline; no extra cost in Postgres	Same — inlined by default
Parameterization	Cannot accept parameters	Cannot either (use functions)
Discoverability	Visible in information_schema	Invisible outside the query

-- Use a VIEW: reused in many places, needs access control
CREATE VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, SUM(total) AS revenue
  FROM orders GROUP BY 1;

-- Use a CTE: decompose a single complex query for readability
WITH base AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS rn
  FROM orders
)
SELECT * FROM base WHERE rn = 1;

Rule of thumb: use a view when you need to share, reuse, or restrict access to a query across the codebase. Use a CTE when you need to decompose a single query for readability — it disappears after the query runs.

A regular view executes its underlying query every time it is queried — the data is always current but the query cost is paid on every access.

A materialized view stores the query result on disk like a table. Reads are instant (no recomputation), but the data is stale until explicitly refreshed.

-- Regular view: always fresh, always recomputes
CREATE VIEW monthly_sales AS
  SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1;

-- Materialized view (Postgres): fast reads, manual refresh
CREATE MATERIALIZED VIEW monthly_sales_mv AS
  SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1;

-- Refresh (blocks reads unless CONCURRENTLY is used)
REFRESH MATERIALIZED VIEW monthly_sales_mv;

-- Non-blocking refresh (requires a unique index)
CREATE UNIQUE INDEX ON monthly_sales_mv (month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_mv;

Rule of thumb: use a regular view when data must be current; use a materialized view for expensive aggregations where slightly stale data is acceptable (dashboards, reports). Schedule the refresh as a cron job.

In Postgres and SQL Server, views are inlined by the optimizer — the query planner substitutes the view definition into the outer query and optimizes the whole thing as a single query. There is typically no performance difference between querying through a view and writing the equivalent SQL directly.

-- These two are optimized identically in Postgres:
SELECT * FROM active_users WHERE name = 'Alice';

SELECT * FROM (
  SELECT id, name, email FROM users WHERE deleted_at IS NULL
) sub WHERE name = 'Alice';
-- Both produce the same plan: index scan on (name) with deleted_at IS NULL pushed down.

Exception: views with DISTINCT, LIMIT, subqueries in SELECT, or window functions can prevent full predicate pushdown — check with EXPLAIN.

Rule of thumb: view indirection is free in most cases. Always use EXPLAIN ANALYZE to confirm that predicates from the outer query are pushed inside the view's definition; if they are not, the view may force a full scan.

By granting SELECT on a view (not the base table), you restrict what data each role can see. The view acts as a security boundary: callers only see rows the view exposes.

-- Base table: orders (all customers)
-- View: each salesperson sees only their own customer's orders

CREATE VIEW my_orders AS
  SELECT o.*
  FROM   orders o
  JOIN   salespeople s ON s.customer_id = o.customer_id
  WHERE  s.username = current_user;   -- session-level user

-- Grant only the view, not the table
GRANT SELECT ON my_orders TO salesperson_role;
REVOKE ALL ON orders FROM salesperson_role;

Postgres also offers Row-Level Security (RLS) as a more flexible alternative that enforces policies at the table level.

Rule of thumb: views-as-security-boundaries work well for simple, role-based access patterns. For fine-grained, data-driven access control (multi-tenant apps), prefer Postgres RLS — it applies even when queries bypass the view and hit the table directly.

Views store the query text, not the resolved columns. The behavior on schema change differs by database:

• Postgres: if you SELECT * in the view and add a column to the base table, the view does not automatically include the new column — the * is expanded at view creation time. To pick up the new column, you must CREATE OR REPLACE VIEW. If you drop a column referenced in the view, querying the view raises an error.
• MySQL: views are re-parsed on each access, so dropping a referenced column makes the view fail at query time (not at drop time).
• SQL Server: views can be refreshed with sp_refreshview to re-resolve * expansions after schema changes.

-- Check for broken views in Postgres
SELECT schemaname, viewname
FROM   pg_views v
WHERE  NOT EXISTS (
  SELECT 1 FROM information_schema.view_table_usage
  WHERE  view_name = v.viewname
);

-- Or simply try:
SELECT * FROM problematic_view LIMIT 0;
-- Will surface a dependency error immediately.

Rule of thumb: avoid SELECT * in view definitions — always list columns explicitly. Run a migration smoke-test against all views after any table schema change.

Yes — in Postgres and SQL Server you can create a view that wraps a recursive CTE, enabling callers to query hierarchical data (trees, graphs) without writing the recursion each time.

-- Postgres: recursive view for an employee org chart
CREATE RECURSIVE VIEW org_chart (id, name, manager_id, depth, path) AS (
  -- Anchor: top-level employees
  SELECT id, name, manager_id, 0, ARRAY[id]
  FROM   employees
  WHERE  manager_id IS NULL

  UNION ALL

  -- Recursive: employees whose manager is already in the result
  SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || e.id
  FROM   employees e
  JOIN   org_chart oc ON oc.id = e.manager_id
);

-- Callers query it without knowing it's recursive
SELECT * FROM org_chart WHERE depth <= 3 ORDER BY path;

Rule of thumb: wrap recursive CTEs in a view when the hierarchy query is reused across multiple callers. Set a depth limit inside the CTE to guard against infinite loops from cyclic data.

An indexed view (SQL Server's term for a materialized view) is a view with a clustered unique index created on it. SQL Server physically stores the result set and updates it as the underlying data changes (unlike Postgres, which requires a manual REFRESH).

-- Must use SCHEMABINDING to prevent base table changes from breaking the view
CREATE VIEW dbo.daily_revenue
WITH SCHEMABINDING AS
  SELECT
    CONVERT(DATE, created_at) AS day,
    SUM(total)                AS revenue,
    COUNT_BIG(*)              AS order_count   -- COUNT_BIG required for indexed views
  FROM dbo.orders
  GROUP BY CONVERT(DATE, created_at);
GO

-- Create the clustered index to materialize it
CREATE UNIQUE CLUSTERED INDEX ix_daily_revenue_day
  ON dbo.daily_revenue (day);

The optimizer can automatically use the indexed view to satisfy matching queries against the base table — even if the query doesn't mention the view.

Rule of thumb: use indexed views in SQL Server for expensive, frequently queried aggregations that can tolerate the write overhead of keeping the materialized result up to date automatically.

A view is a fixed query — it cannot accept parameters. A table-valued function (TVF) looks like a table to callers but accepts arguments, making it a parameterizable view.

-- Postgres: table-valued function
CREATE FUNCTION orders_for_customer(p_customer_id INT)
RETURNS TABLE (id INT, total NUMERIC, created_at TIMESTAMPTZ)
LANGUAGE SQL STABLE AS $$
  SELECT id, total, created_at
  FROM   orders
  WHERE  customer_id = p_customer_id;
$$;

-- Call it like a table
SELECT * FROM orders_for_customer(42) WHERE total > 100;

-- SQL Server equivalent (inline TVF)
CREATE FUNCTION dbo.OrdersForCustomer(@CustomerId INT)
RETURNS TABLE AS
RETURN (
  SELECT id, total, created_at FROM orders WHERE customer_id = @CustomerId
);
SELECT * FROM dbo.OrdersForCustomer(42) WHERE total > 100;

The optimizer inlines simple TVFs just like views, so they are as fast as writing the query directly.

Rule of thumb: use a view when the query is the same for all callers; use a table-valued function when callers need to pass a filter parameter that determines which rows are returned.

-- Postgres: view dependencies via information_schema
SELECT vtu.view_name,
       vtu.table_name,
       vtu.column_name
FROM   information_schema.view_column_usage vtu
WHERE  vtu.view_name = 'active_users'
ORDER  BY vtu.table_name, vtu.column_name;

-- Postgres: all objects that depend on a table (to check before DROP)
SELECT dependent_ns.nspname AS schema,
       dependent_view.relname AS dependent_view
FROM   pg_depend
JOIN   pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN   pg_class dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN   pg_class source_table   ON pg_depend.refobjid  = source_table.oid
JOIN   pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE  source_table.relname = 'users'
  AND  dependent_view.relname <> 'users';

-- SQL Server
SELECT referencing_entity_name
FROM   sys.dm_sql_referencing_entities('dbo.users', 'OBJECT');

Rule of thumb: always check view dependencies before dropping or altering a table. In Postgres, DROP TABLE … CASCADE will silently drop all dependent views — use it only when that is intentional.

WITH SCHEMABINDING binds the view to the schema of the referenced base tables. While bound, the database prevents any change to the base tables that would break the view — you cannot DROP a referenced column or the table itself without first dropping or unbinding the view.

-- SQL Server: create a schema-bound view
CREATE VIEW dbo.product_summary
WITH SCHEMABINDING AS
  SELECT p.id,
         p.name,
         c.name AS category
  FROM   dbo.products p          -- must use two-part names (schema.table)
  JOIN   dbo.categories c ON c.id = p.category_id;

-- Attempt to drop a referenced column will now fail:
-- ALTER TABLE dbo.products DROP COLUMN name;
-- ERROR: cannot drop column because it is referenced by object 'product_summary'

SCHEMABINDING is also a prerequisite for creating an indexed view (materialized view) in SQL Server — without it, the clustered index creation will be rejected.

Rule of thumb: use WITH SCHEMABINDING on production views in SQL Server to prevent accidental schema changes from silently breaking them, and always use it when you plan to add a clustered index to the view.