All about Views in SQL

All About Views in SQL Server

Understanding regular, updateable, and indexed views with syntax, examples, and restrictions

🔹 What is a View?

A View is any relation not part of the logical model but made visible to a user as a virtual table. It is based on a SELECT query and can simplify complex queries, enhance security, and provide abstraction.

🔹 Syntax

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ]

<view_attribute> ::=
{
  [ ENCRYPTION ]
  [ SCHEMABINDING ]
  [ VIEW_METADATA ]
}

View Attributes

• ENCRYPTION – Hides the view definition from everyone, including sysadmin.
• SCHEMABINDING – Prevents dropping/altering referenced objects without dropping the view first.
• VIEW_METADATA – Returns metadata of the view instead of base tables.

🔹 Example – Simple View

CREATE VIEW v_CustomerAddress
AS
SELECT a.CustomerID, a.CustomerName, c.AddressLine1, c.AddressLine2, c.AddressLine3,
       c.City, d.StateProvince, c.PostalCode, e.Country
FROM dbo.Customer a
INNER JOIN dbo.CustomerToCustomerAddress b ON a.CustomerID = b.CustomerID
INNER JOIN dbo.CustomerAddress c ON b.CustomerAddressID = c.CustomerAddressID
INNER JOIN dbo.StateProvince d ON c.StateProvinceID = d.StateProvinceID
INNER JOIN dbo.Country e ON c.CountryID = e.CountryID;

🔹 Restrictions on SELECT in a View

• No COMPUTE or COMPUTE BY
• No INTO or OPTION
• No temporary tables or table variables
• ORDER BY only allowed with TOP

🔹 Updateable View

CREATE VIEW dbo.v_Customer
AS
SELECT CustomerID, CustomerName, CreditLine, AvailableCredit
FROM dbo.Customer
WHERE CreditLine > 1000
WITH CHECK OPTION;

INSERT INTO dbo.Customer (CustomerName, CreditLine)
VALUES ('Customer1', 5000);

🔹 Indexed (Materialized) Views

An Indexed View stores the result set physically on disk by creating a clustered index on it. This can improve performance for aggregation-heavy queries but comes with strict prerequisites and restrictions.

Restrictions

• Cannot reference other views
• All functions must be deterministic (e.g., no GETDATE())
• No AVG, MIN, MAX, STDEV

Regular vs Indexed View

Feature	Regular View	Indexed View
Data Storage	No	Yes (materialized)
Performance	Same as base query	Faster for repeated queries
Restrictions	Few	Many

🔹 Example – Indexed View

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
       OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);

Note: The query optimizer can automatically use indexed views if all required SET options are enabled.