Views: Saved Queries

Five analysts all need "total runs per team". If each one writes their own GROUP BY, someone will get it wrong. A view lets you define that logic once in the database, and everyone queries it like a table.

Definition

A view is a virtual table defined by a stored SELECT query. The database saves only the query definition, not the data. Every time someone selects from the view, the database runs the saved query against the underlying (base) tables and returns the current rows.

• Virtual table — it has columns and rows and you query it with SELECT … FROM view_name, but it does not physically hold rows.
• Stored query — the SELECT text is saved permanently in the database (until you DROP VIEW).
• Always current — because it runs live, it always reflects the latest data in the base tables.

The flow: Tables (physical data on disk) → View (a saved SELECT) → Users / reports (analysts, BI tools, apps) query the view.

Views vs tables

Syntax

CREATE VIEW view_name AS
SELECT column_list
FROM table_name
WHERE condition;

ALTER VIEW view_name AS          -- change the definition
SELECT ...;

DROP VIEW view_name;             -- remove the view (base tables are untouched)

Tip: Name views with a v_ prefix (v_team_totals) so everyone knows it is a view, not a table.

Example — define logic once

CREATE VIEW v_team_totals AS
SELECT
    team,
    SUM(runs) AS total_runs,
    COUNT(*)  AS players
FROM players
GROUP BY team;

Then use it like a table:

SELECT * FROM v_team_totals;

SELECT * FROM v_team_totals WHERE total_runs >= 700;

Change the view once → every report that uses it is updated.

Example — a view is always current

UPDATE players SET runs = runs + 400 WHERE name = 'MS Dhoni';   -- 350 → 750
SELECT * FROM v_team_totals WHERE total_runs >= 700;

CSK appears now — nobody refreshed the view; it simply re-ran its query on the new data.

Why use views? Four classic reasons

CREATE VIEW v_report AS
SELECT p.name, t.team_name, t.city, p.runs
FROM players AS p
INNER JOIN teams AS t
    ON p.team = t.team;

SELECT * FROM v_report ORDER BY runs DESC;

Views vs CTEs

Both hide complexity — but their lifetime differs.

Rules and common mistakes

• ❌ ORDER BY inside a view → not allowed in SQL Server (unless with TOP). ✅ sort when you query the view.
• ❌ Expecting a view to make a slow query fast → a normal view re-runs its query every time. ✅ use an indexed view (SQL Server's materialized view) or a CTAS table for speed.
• ❌ Parameters in a view (WHERE team = @team) → views cannot take parameters. ✅ filter when you query it, or use a stored procedure / table-valued function.
• ❌ In SSMS, CREATE VIEW mixed with other statements → error "CREATE VIEW must be the only statement in the batch". ✅ separate batches with GO.
• ❌ Dropping or renaming a column the view uses → the view breaks. ✅ WITH SCHEMABINDING prevents such changes to the base table.
• Views over a single table can sometimes be updated (UPDATE v_rcb_players … changes players). WITH CHECK OPTION stops inserts/updates that would fall outside the view's WHERE.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Does a view store data? No. A normal view stores only its SELECT definition; the data stays in the base tables and the query runs each time. (The exception is an indexed / materialized view, which physically stores the result.)