Views

A view is a saved SELECT statement with a name. From the outside it looks and behaves exactly like a table — you can SELECT from it, filter it with WHERE, join it to other tables, and grant or deny access to it independently from the underlying tables. On the inside it is a query definition stored in the database catalog that runs every time the view is referenced. Views serve three distinct purposes: they simplify complex queries by hiding JOIN and aggregation logic behind a clean name, they enforce security by exposing only the columns and rows a user is permitted to see, and they provide a stable interface that insulates applications from underlying schema changes. This lesson covers creating, modifying, and managing views in DataplexaStore — including the restrictions that apply and the important distinction between regular and indexed views.

Creating a View

The CREATE VIEW statement names the view and defines the SELECT query it wraps. The SELECT can be as simple or complex as needed — joins, aggregations, expressions, subqueries, and window functions are all valid. Once created the view appears in the database alongside tables and can be queried exactly like one. The convention for naming views is to prefix them with vw_ so they are immediately distinguishable from base tables in any query or schema browser. Views do not store data — every query against a view executes the underlying SELECT fresh, against the current state of the base tables.

-- Create a view that joins Users and Orders for a clean customer order summary
USE DataplexaStore;

CREATE VIEW vw_CustomerOrders AS
SELECT
    u.UserID,
    u.FullName,
    u.Email,
    u.Country,
    u.MembershipTier,
    o.OrderID,
    o.OrderDate,
    o.Status,
    o.TotalAmount
FROM   Users  u
JOIN   Orders o ON u.UserID = o.UserID;

-- Query the view exactly like a table
SELECT * FROM vw_CustomerOrders ORDER BY TotalAmount DESC;

-- Filter the view — WHERE applies on top of the view's own query
SELECT FullName, OrderID, TotalAmount, Status
FROM   vw_CustomerOrders
WHERE  Status = 'delivered'
ORDER BY TotalAmount DESC;

• The view hides the JOIN — any query against vw_CustomerOrders reads clean flat output without needing to know that Users and Orders are separate tables with a UserID relationship
• WHERE Status = 'delivered' is applied after the view's query runs — SQL Server's optimiser often pushes predicates down into the view's query for efficiency, but the logical behaviour is always filter-after-view
• Bob Williams appears twice because he has two orders — the view returns one row per order, not one row per user; this is the correct behaviour for an order-centric view

Aggregated Views

Views can contain GROUP BY and aggregate functions, turning complex summarisation logic into a reusable named object. An aggregated view pre-defines the grouping and the metrics — any query against it can then filter, sort, and join on those pre-computed labels. This is particularly useful for reporting layers where multiple dashboards and reports need the same summary figures but should not each contain a copy of the same GROUP BY logic.

-- Aggregated view — product sales summary
CREATE VIEW vw_ProductSales AS
SELECT
    p.ProductID,
    p.ProductName,
    p.Category,
    p.Price,
    p.StockQty,
    COUNT(oi.OrderItemID)               AS times_ordered,
    ISNULL(SUM(oi.Quantity), 0)         AS units_sold,
    ISNULL(SUM(oi.Quantity * oi.UnitPrice), 0) AS revenue_generated,
    AVG(r.Rating)                       AS avg_rating,
    COUNT(r.ReviewID)                   AS review_count
FROM       Products   p
LEFT JOIN  OrderItems oi ON p.ProductID = oi.ProductID
LEFT JOIN  Reviews    r  ON p.ProductID = r.ProductID
GROUP BY   p.ProductID, p.ProductName, p.Category, p.Price, p.StockQty;

-- Query the aggregated view
SELECT
    ProductName,
    Category,
    units_sold,
    revenue_generated,
    avg_rating,
    review_count
FROM   vw_ProductSales
ORDER BY revenue_generated DESC;

• LEFT JOIN on both OrderItems and Reviews means products with no sales or no reviews still appear — ISNULL converts the NULL sums to 0 for clean numeric output, while avg_rating correctly stays NULL (not 0) for unreviewed products since a 0 rating is a real rating and NULL means "no data"
• Monitor 27 inch generates the highest revenue (719.98) despite being sold in fewer units (2) than Notebook A5 (8 units, 103.92 revenue) — a view that shows both metrics makes this trade-off immediately visible without any ad-hoc calculation
• Once this view exists every report, dashboard query, and ad-hoc analysis draws from the same definition — if the calculation changes (for example adding a discount column) it is fixed once in the view and all consumers see the corrected figure automatically

Modifying and Dropping Views

An existing view is modified with ALTER VIEW, which replaces the entire SELECT definition. There is no way to alter just one column or one join in a view — the full SELECT must be rewritten. The safer pattern in deployment scripts is CREATE OR ALTER VIEW, available since SQL Server 2016, which creates the view if it does not exist or replaces it if it does — making the script safely re-runnable without needing to check for prior existence. DROP VIEW removes the view definition from the catalog but leaves the underlying base tables completely untouched.

-- ALTER VIEW — add the JoinDate column to vw_CustomerOrders
ALTER VIEW vw_CustomerOrders AS
SELECT
    u.UserID,
    u.FullName,
    u.Email,
    u.Country,
    u.MembershipTier,
    u.JoinDate,                          -- newly added column
    o.OrderID,
    o.OrderDate,
    o.Status,
    o.TotalAmount
FROM   Users  u
JOIN   Orders o ON u.UserID = o.UserID;

-- CREATE OR ALTER — idempotent, safe to run multiple times
CREATE OR ALTER VIEW vw_UnorderedProducts AS
SELECT
    ProductID,
    ProductName,
    Category,
    Price,
    StockQty
FROM   Products
WHERE  ProductID NOT IN (SELECT DISTINCT ProductID FROM OrderItems);

-- Inspect view definitions stored in the catalog
SELECT
    v.name                  AS view_name,
    v.create_date,
    v.modify_date,
    m.definition            AS view_sql
FROM   sys.views            v
JOIN   sys.sql_modules      m ON v.object_id = m.object_id
WHERE  v.name LIKE 'vw_%'
ORDER BY v.name;

-- Query the new view
SELECT * FROM vw_UnorderedProducts ORDER BY Category, Price;

• ALTER VIEW rewrites the entire view definition — the old definition is discarded completely; the modify_date in sys.views reflects when it was last altered
• CREATE OR ALTER is the deployment-safe form — running it twice is harmless, unlike CREATE VIEW which fails on the second run with "object already exists"
• sys.sql_modules stores the full text of every view, stored procedure, and function definition — querying it is how you retrieve a view's SQL without using SSMS's GUI

View Restrictions and WITH SCHEMABINDING

Standard views have several restrictions that catch developers by surprise. A view cannot use ORDER BY unless it also uses TOP, OFFSET/FETCH, or FOR XML — ORDER BY in a view without one of these is a syntax error because the relational model defines a table as an unordered set, and a view is a virtual table. The order of rows returned from a view is controlled by the ORDER BY in the query that selects from the view, not inside the view itself. WITH SCHEMABINDING is an option that binds the view to the schema of the underlying tables — it prevents any ALTER TABLE or DROP TABLE on the base tables that would break the view, protecting the view definition from silent breakage. Schemabinding is also required before a view can have an index built on it.

-- WITH SCHEMABINDING — protect the view from base table changes

CREATE OR ALTER VIEW vw_ActiveInventory
WITH SCHEMABINDING                       -- binds view to base table schema
AS
SELECT
    p.ProductID,
    p.ProductName,
    p.Category,
    p.Price,
    p.StockQty
FROM   dbo.Products p                    -- schema prefix required with SCHEMABINDING
WHERE  p.StockQty > 0;

-- Verify schemabinding is set
SELECT
    name,
    is_schema_bound
FROM   sys.views
WHERE  name = 'vw_ActiveInventory';

-- Attempt to drop a column used by a schemabinding view — must fail
ALTER TABLE Products DROP COLUMN StockQty;

• is_schema_bound = 1 confirms the binding is active — SQL Server now tracks the dependency between the view and every column and table it references
• The DROP COLUMN attempt was blocked — without schemabinding the column would have been dropped silently, leaving the view broken and returning an error only when someone next queried it; schemabinding catches this at ALTER time instead
• The dbo. schema prefix on table names is mandatory with SCHEMABINDING — SQL Server requires two-part names (schema.table) to resolve dependencies unambiguously

Using Views for Security — Column and Row Filtering

One of the most practical uses of views is access control. By granting a user SELECT permission on a view rather than the base table you can precisely control which columns they see (column-level security) and which rows they see (row-level security). A view that excludes an Email column, for example, can be granted to a reporting role without exposing personal contact data. A view that filters with WHERE Country = 'UK' can be granted to a regional team who should only ever see their own region's data. The underlying table is never directly accessible to those users — they only see what the view exposes.

-- Security view — expose order data without revealing personal user details
CREATE OR ALTER VIEW vw_OrderReport AS
SELECT
    o.OrderID,
    o.OrderDate,
    o.Status,
    o.TotalAmount,
    u.Country,
    u.MembershipTier         -- tier visible for segmentation
    -- u.Email, u.FullName intentionally excluded for privacy
FROM   Orders o
JOIN   Users  u ON o.UserID = u.UserID;

-- Row-filtering view — only show UK orders (regional access control)
CREATE OR ALTER VIEW vw_UKOrders AS
SELECT
    o.OrderID,
    o.OrderDate,
    o.Status,
    o.TotalAmount,
    u.FullName,
    u.MembershipTier
FROM   Orders o
JOIN   Users  u ON o.UserID = u.UserID
WHERE  u.Country = 'UK';     -- row filter baked into the view definition

SELECT * FROM vw_OrderReport  ORDER BY TotalAmount DESC;
SELECT * FROM vw_UKOrders     ORDER BY OrderDate;

• vw_OrderReport exposes no Email or FullName — a reporting user granted access to this view cannot retrieve personal contact data even indirectly, because the underlying Users table is not directly accessible to them
• vw_UKOrders enforces the row filter at the view level — a UK regional analyst granted access to this view literally cannot see orders from Canada or South Korea, even by adding their own WHERE clause, because those rows are excluded before the view result is returned
• Granting GRANT SELECT ON vw_UKOrders TO [regional_analyst_role] without granting any access to the base tables is the correct security pattern — the view is the only access point

Summary Table

Practice Questions

Quiz

Next up - Indexes - Understand how SQL Server uses indexes to find rows instantly and learn which columns to index, when indexes hurt as much as they help, and how to keep them healthy.