What a View Is
A view is a named, stored SELECT statement that behaves like a virtual table: querying a view runs its underlying query against the base tables each time, so the view itself stores no data (unless it is indexed, discussed below), only the query definition. Views are useful for hiding query complexity behind a simple name, restricting which columns or rows a group of users can see without granting them direct table access, and providing a stable interface that shields applications from underlying schema changes as long as the view's output shape stays the same.
Cricket analogy: A view is like a broadcaster's 'Player of the Match' graphic — it's not a separately stored stat, it's recalculated live from the underlying ball-by-ball data every time it's displayed, always reflecting the current match state.
CREATE VIEW sales.vw_ActiveCustomerOrders
AS
SELECT c.CustomerId, c.CustomerName, o.OrderId, o.OrderDate, o.TotalAmount
FROM sales.Customers AS c
JOIN sales.Orders AS o ON o.CustomerId = c.CustomerId
WHERE c.IsActive = 1
AND o.OrderStatus <> 'Cancelled';
GO
-- Consumers query it exactly like a table
SELECT CustomerName, OrderId, TotalAmount
FROM sales.vw_ActiveCustomerOrders
WHERE TotalAmount > 1000;Schemas as Namespaces
A schema is a namespace that groups database objects (tables, views, procedures) under a name like sales or hr, referenced as schema.objectname; the default schema is dbo, but organizing objects into purpose-specific schemas makes large databases easier to navigate and lets you grant permissions at the schema level instead of object by object. Because permissions can be assigned to a schema as a whole (GRANT SELECT ON SCHEMA::reporting TO ReportingRole), adding a new table to that schema automatically extends the same access to it without a separate GRANT statement for every new object.
Cricket analogy: A schema is like organizing a stadium's stat archive into separate sections — batting, bowling, fielding — so a fielding analyst only needs access to the fielding section, not the whole archive.
Schema Binding and Indexed Views
A view created WITH SCHEMABINDING locks the underlying tables' referenced columns against changes that would break the view — you cannot DROP or ALTER those columns while the schema-bound view exists — and schema binding is a prerequisite for creating an indexed (materialized) view, where a unique clustered index actually stores the view's result set physically, trading storage and write overhead for read performance similar to a regular table. Because an indexed view is materialized, it can dramatically speed up aggregate queries (like a daily sales summary) that would otherwise recompute the aggregation from scratch on every SELECT, at the cost of every INSERT/UPDATE/DELETE on the base tables having to maintain the view's index too.
Cricket analogy: An indexed view is like a scoreboard operator pre-calculating and physically posting the tournament's net run rate table after every match instead of recalculating it live from scratch each time a fan asks to see it.
SCHEMABINDING requires the view's SELECT list to use two-part names (schema.table), forbids SELECT *, and blocks structural changes to referenced columns unless the view is dropped or altered first — this is the price of the safety guarantee it provides.
An updatable view (one without GROUP BY, DISTINCT, or aggregates, referencing a single base table) allows INSERT/UPDATE/DELETE through it, but changes must satisfy the base table's constraints, and if the view's WHERE clause excludes the row you just inserted, it silently becomes invisible through that view unless you added WITH CHECK OPTION to prevent that.
- A view is a stored SELECT statement, not stored data — it re-runs its query against base tables every time it's queried.
- Views can hide query complexity, restrict column/row visibility, and insulate applications from schema changes.
- Schemas are namespaces (schema.objectname) that organize objects and let permissions be granted at the schema level.
- GRANT on a schema automatically extends to new objects added to that schema without individual GRANT statements.
- WITH SCHEMABINDING locks referenced columns against changes and is required before creating an indexed view.
- An indexed view physically materializes its result set via a unique clustered index, trading write cost for read speed.
- WITH CHECK OPTION prevents inserts/updates through a view from producing rows that the view's own WHERE clause would then hide.
Practice what you learned
1. What does a standard (non-indexed) SQL Server view actually store?
2. What is the default schema in SQL Server if none is specified?
3. What must be true before you can create an indexed view?
4. What does WITH CHECK OPTION prevent on an updatable view?
5. What is a benefit of granting permissions at the schema level rather than per object?
Was this page helpful?
You May Also Like
Constraints and Keys
Learn how PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, and NOT NULL constraints enforce data integrity directly at the database layer.
Indexes and Performance
Learn how clustered and nonclustered indexes speed up SQL Server queries, and how to read execution plans and statistics to diagnose slow queries.
Temp Tables and Table Variables
Compare local temp tables, global temp tables, and table variables in SQL Server, and learn when each is the right tool for intermediate result sets.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics