What is a database view, and what problems do teams typically create one to solve?
answer
- stored query, zero storage
- always current, nothing to refresh
- grant on the view, not the base table
- facade over a moving schema
- no free speed; hides real cost
basics
~20 sA view is a named query stored in the database. Selecting from it runs the underlying query against the live tables — it holds no data of its own. Teams use views to name a complex query once, hide columns or rows for access control, and give applications a stable shape while the tables underneath change.
solid answer
~60 sA view is a stored, named query definition. It occupies no storage and caches nothing: every reference re-executes its definition against the current base tables, so readers always see current data under their transaction's snapshot. The common reasons to create one: - **Naming and reuse.** A gnarly join used by six reports becomes one named object, so the logic is defined and fixed in one place. - **Access control.** Grant SELECT on a view that omits salary and national ID, grant nothing on the base table, and callers physically cannot read what the view does not project. - **Interface stability.** A view is a facade: rename or split the tables beneath and keep the view's output shape constant, so readers do not break. - **Simplification.** Consumers with limited SQL — BI tools, analysts — get a flat, meaningful shape instead of a nine-table join. The cost is that abstraction hides work: a cheap-looking SELECT may expand into a very expensive plan, and stacked views make that easy to miss.
code
sql · 5 linesCREATE VIEW active_customers AS
SELECT customer_id, name, email
FROM customers
WHERE deleted_at IS NULL
AND email_verified = TRUE;go deeper
Say it is a stored query rather than stored data, and give two real uses: naming a complex query and hiding columns.
Add the mechanics — definition expanded at query time, privileges granted on the view rather than the base table, and no inherent performance gain.
Frame views as a contract surface: they decouple readers from the physical schema, and their cost is invisible at the call site, which is what actually causes incidents.
Decide the policy — which views are public contracts, who may change them, and where the boundary sits between a curated view layer and letting consumers query base tables.
## Definition A view is a database object that stores a query, not a result. Creating one writes the definition into the catalog and allocates no data pages. When a statement names the view, the engine substitutes the definition and evaluates it against the base tables at that moment. Two consequences follow directly: a view is always as fresh as its inputs, and a view has no maintenance cost of its own when the tables change. This is worth stating precisely in an interview because the most common junior error is describing a view as a saved copy of rows that needs refreshing. A separate object type — the materialized view — does store results and does need refreshing, and it is a different tool with a different trade-off (staleness in exchange for precomputed cost). ## Why teams create views **Naming a concept.** Business logic in SQL tends to be repeated: "active customer" might mean not deleted, verified, and with a subscription in a non-cancelled state. Encoding that once in a view named active_customers means every report agrees on the definition, and changing the definition is one edit instead of a search across a codebase. This is the same argument as extracting a function. **Access control by projection.** Views are a classic authorisation surface. Grant SELECT on employees_public, which selects id, name and department but not salary or national ID; grant nothing on employees. A caller now has no path to the hidden columns — the restriction is enforced by the engine, not by convention or by an application layer that might be bypassed. The same works row-wise: a view with WHERE tenant_id = current_setting-style scoping restricts which rows a role can read at all. Note the enforcement usually relies on the view executing with its definer's privileges rather than the caller's, which is exactly why the caller needs no rights on the base table. **A stable interface over a moving schema.** Because the view controls the output shape, you can rename a column, split a table, or change a data type underneath and keep the view's contract identical by adjusting only the definition. Readers keep working. This makes views the standard tool for schema evolution without a coordinated release. **Simplification for humans and tools.** A BI tool pointed at a normalised OLTP schema produces bad joins written by people who do not know the model. A curated set of views gives them correct joins and meaningful column names, and centralises the modelling decisions with the team that owns the data. **Deprecation and compatibility shims.** When a table is retired, a view carrying its old name and shape lets old readers keep working while new code migrates. ## What views do not give you They do not make queries faster by themselves. The optimizer expands the view definition into the calling query and plans the whole thing; nothing is precomputed and nothing is cached because it is a view. If the underlying query is slow, every reader inherits that cost. They do not add security by obscurity: a view whose definition can be read tells the reader exactly which tables exist. Security comes from the privilege grants, not from the view hiding the shape of the schema. And they do not solve write access on their own. A view is writable only under specific conditions, and complex views are read-only unless you add a trigger to handle writes. ## The main hazard Abstraction hides cost. `SELECT * FROM order_summary WHERE order_id = 5` looks like a primary-key lookup. If order_summary joins eight tables and aggregates, the real work may be enormous, and a developer reading the application code has no signal. The problem compounds when views are built on views: each layer adds joins and columns nobody downstream needs, and the final plan can touch far more data than the question requires. Teams that use views heavily should be reading actual plans, not just the SQL text, and should treat a shared view's definition as a contract that cannot be casually widened. ## Interview framing Open with "a stored query, not stored data", list three or four concrete uses with an example each, and close with the honest cost: no performance benefit by itself, and hidden expense that is easy to miss. If you name the materialized-view contrast in one clause, you show you know the difference without wandering off the question.
- Does creating a view make queries against the underlying tables faster?No. A plain view precomputes nothing and caches nothing; the optimizer expands its definition into the calling query and plans the combined statement. Any speedup has to come from indexes, better predicates, or a materialized view that actually stores results. A view can even hurt, by making an expensive query look trivial at the call site.
- How is a view different from a materialized view?A view stores only the query definition and re-evaluates it on every reference, so results are always current and there is nothing to maintain. A materialized view stores the computed result set, so reads can be far cheaper but the data is stale between refreshes and the refresh has an ongoing cost. The choice is freshness versus precomputed work.
A view is a saved search, not a saved list. Reopening it runs the search again against whatever is there now — which is why it is never stale, and why it is never free.
saying these in an interview costs you the question
- Describing a plain view as a stored copy of rows that needs refreshing
- Claiming a view speeds up the query it wraps
- Treating a view as security when the caller still holds privileges on the base table
- Saying views are always read-only
- Assuming a simple-looking SELECT on a view implies a simple plan