SQL for .NET developers Intermediate

Views

A view is a saved SELECT that you can query like a table. Views hide complex joins, give each app or user only the columns they need, and, as indexed views, can pre-calculate totals over millions of rows.

Where a view sits
  1. Your app or reportSELECT * FROM vw_OrderSummary WHERE OrderedAt >= '2026-10-01'
  2. The viewA saved SELECT with the joins and totals; it stores no rows
  3. Indexed view (optional)Adds a clustered index, so the result is stored and kept up to date
  4. Base tablesOrders, OrderLines and Customers hold the real data

A normal view is replaced by its query at run time. Only an indexed view keeps its own copy of the result.

The idea in plain words

A view stores a query, not data. When you SELECT from a view, SQL Server replaces the view name with its query and runs it against the real tables, so the result is always up to date.

Use a view when the same join or calculation is written in many places: reports, the app and ad hoc queries all read one definition, and if the logic changes you fix it once.

Example: an order summary view

SQL
CREATE OR ALTER VIEW dbo.vw_OrderSummary
AS
SELECT o.OrderId,
       o.OrderedAt,
       c.Name  AS CustomerName,
       c.Email AS CustomerEmail,
       COUNT(ol.OrderLineId)            AS Lines,
       SUM(ol.Quantity * ol.UnitPrice)  AS Total
FROM dbo.Orders o
JOIN dbo.Customers c   ON c.CustomerId = o.CustomerId
JOIN dbo.OrderLines ol ON ol.OrderId   = o.OrderId
GROUP BY o.OrderId, o.OrderedAt, c.Name, c.Email;
GO

-- Query it like a table
SELECT TOP (20) * FROM dbo.vw_OrderSummary
WHERE OrderedAt >= '2026-10-01'
ORDER BY Total DESC;

Views for security

A view can expose some columns and rows of a table without giving access to the table itself. Grant SELECT on the view and not on the table, and a reporting user can see order totals without ever seeing password hashes or phone numbers.

SQL
CREATE ROLE Reporting;
GRANT SELECT ON dbo.vw_OrderSummary TO Reporting;
-- no permission on dbo.Customers itself

Indexed views for huge data

A normal view runs its query every time. On a table with tens of millions of rows, a SUM over everything can take seconds. An indexed view stores its result on disk like a table, and SQL Server keeps it up to date on every insert, update and delete. Reading the totals then takes milliseconds.

The price is paid on writes: every change to the base tables also updates the view's index. Use indexed views for data that is read far more often than it changes, such as daily sales totals.

SQL
CREATE VIEW dbo.vw_DailySales
WITH SCHEMABINDING                 -- required: locks the table definitions
AS
SELECT CAST(o.OrderedAt AS date)          AS Day,
       SUM(ol.Quantity * ol.UnitPrice)    AS Revenue,   -- columns must be NOT NULL
       COUNT_BIG(*)                       AS LineCount  -- required with GROUP BY
FROM dbo.Orders o
JOIN dbo.OrderLines ol ON ol.OrderId = o.OrderId
GROUP BY CAST(o.OrderedAt AS date);
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_DailySales ON dbo.vw_DailySales (Day);

Common mistakes

  • SELECT * in a view. Columns added to the table later are not picked up until the view is refreshed, and you send columns nobody needs.
  • ORDER BY in a view. It is not allowed without TOP, and the caller must sort anyway.
  • Views built on views built on views. The final query becomes huge and hard to tune; keep nesting shallow.
  • Expecting a normal view to be faster than the query inside it. Only an indexed view stores results.

Interview question

What is the difference between a view and an indexed view?

A normal view stores only its query; each SELECT from it runs that query against the base tables, so it costs the same as writing the query yourself. An indexed (materialized) view is created WITH SCHEMABINDING and gets a unique clustered index, so SQL Server stores the result on disk and maintains it on every change to the base tables. Reads become very fast, especially aggregates over large tables, but inserts, updates and deletes do extra work.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.