A view is a named SELECT statement that produces one result set and can be queried like a table. It is often called a virtual table, but it does not store its own data; it retrieves data through its underlying query. Views make repeated or complex queries easier to reuse and can be permission-controlled. section 20.0 section 20.1
Create a reusable title-and-category view
Suppose a title list repeatedly needs each title’s category. Save the join as vw_TitleCategory, then select from vw_TitleCategory just as you would select from a table. The result matches the original join, while later queries need less repeated SQL. section 20.1.1
CREATE VIEW vw_TitleCategory
AS
SELECT
t.TitleName,
c.CategoryName
FROM title t
INNER JOIN category c
ON t.CategoryID = c.CategoryID;
This complete CREATE VIEW statement saves a join between title and category under a meaningful view name. section 20.1.1
After creating a view, filter, order, and aggregate its results in ordinary queries. For example, selecting from vw_TitleCategory with CategoryName = 'Mystery' returns only mystery titles. A view can also join other tables or views, provided its result includes columns that define the needed relationships. section 20.1.2 section 20.1.3
Join a view to calculate sales by category
To count titles ordered by category, add TitleID and CategoryID to vw_TitleCategory, then left join it to orderitem on TitleID and group by CategoryName. Including primary-key values makes the view useful in later joins; display names alone do not establish relationships. section 20.1.3
Use views with a few practical limits in mind:
- Give the view a name distinct from every table and other view; a prefix such as vw_ helps readers recognize it. section 20.1.4
- Ensure every view column has a unique name. Duplicate names, such as two OrderID columns, cause an error. section 20.1.3 section 20.5
- Avoid unnecessary ORDER BY clauses and nested views, which can hurt performance, especially with large result sets. section 20.1.3 section 20.1.4
- Avoid using views to insert or update data, because a change can affect multiple tables. section 20.1.4
Choose a view when one reusable query and one result set are enough, especially when it should participate in joins. Use a stored procedure when work needs parameters, multiple queries, multiple result sets, or data-writing logic; stored procedures cannot be joined to tables or views. section 20.3 section 20.1.4
