CodeInQueries
SQL Server Tutorial: Working with Views (Using
AdventureWorksLT2022)
What is a View?
A view is a virtual table based on the result of an SQL query. It doesn't store data itself but
displays data stored in tables.
Why Use Views?
• Simplify complex queries
• Provide security by exposing only specific columns
• Improve reusability
• Centralize logic for reports or dashboards
Syntax to Create a View
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Basic Example 1: Creating a Simple View
Goal: Display basic customer info
CREATE VIEW vw_BasicCustomerInfo AS
SELECT CustomerID, FirstName, LastName, EmailAddress
FROM [Link];
Test the View
SELECT * FROM vw_BasicCustomerInfo;
Example 2: View with JOIN
Goal: Combine Customer and Address info
CREATE VIEW vw_CustomerWithAddress AS
SELECT
[Link],
[Link],
[Link],
a.AddressLine1,
[Link],
[Link],
[Link],
[Link]
FROM [Link] c
JOIN [Link] ca ON [Link] = [Link]
JOIN [Link] a ON [Link] = [Link];
Query
SELECT * FROM vw_CustomerWithAddress WHERE CountryRegion = 'United States';
Example 3: View with Aggregate Functions
Goal: Total sales by product
CREATE VIEW vw_TotalSalesByProduct AS
SELECT
[Link] AS ProductName,
SUM([Link]) AS TotalSales
FROM [Link] s
JOIN [Link] p ON [Link] = [Link]
GROUP BY [Link];
Query
SELECT * FROM vw_TotalSalesByProduct ORDER BY TotalSales DESC;
Updating Data Through Views
You can update data only if:
• View references a single table
• No aggregate functions
• No GROUP BY, DISTINCT, UNION, etc.
Example: Updatable View
CREATE VIEW vw_ProductPrices AS
SELECT ProductID, Name, ListPrice
FROM [Link];
-- Update list price
UPDATE vw_ProductPrices
SET ListPrice = 25.99
WHERE ProductID = 709;
Advanced View: Parameterized Query Alternative
SQL Server doesn’t support parameterized views, but you can use inline-table valued
functions or apply WHERE filters externally.
-- Using WHERE externally
SELECT * FROM vw_CustomerWithAddress
WHERE StateProvince = 'California';
Security Example: Limited Column Access
Goal: Hide sensitive data like password hash
CREATE VIEW vw_SafeCustomerView AS
SELECT CustomerID, FirstName, LastName, EmailAddress
FROM [Link];
You can grant SELECT on view instead of table.
How to Delete a View
DROP VIEW vw_BasicCustomerInfo;
Best Practices
• Use meaningful view names (prefix vw_)
• Avoid SELECT * in views
• Index views for performance if needed (requires SCHEMABINDING)
Indexed (Materialized) Views
CREATE VIEW vw_SalesWithBinding
WITH SCHEMABINDING
AS
SELECT ProductID, COUNT_BIG(*) AS SaleCount
FROM [Link]
GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX idx_SalesCount
ON vw_SalesWithBinding (ProductID);
Explanation:
• WITH SCHEMABINDING binds the view to the table structure, required for indexed
views.
• COUNT_BIG is required in indexed views.
• CREATE UNIQUE CLUSTERED INDEX materializes the view (stores it physically).
Interview & Practice Questions
1. Create a view showing customers who ordered more than 3 items.
CREATE VIEW vw_HighOrderCustomers AS
SELECT [Link], FirstName, LastName, COUNT([Link]) AS
OrderCount
FROM [Link] c
JOIN [Link] s ON [Link] = [Link]
GROUP BY [Link], FirstName, LastName
HAVING COUNT([Link]) > 3;
2. Can you update data through a view? Give example.
UPDATE vw_ProductPrices
SET ListPrice = 50.00
WHERE ProductID = 700;
3. Create a view showing top 5 products with highest total sales.
CREATE VIEW vw_Top5Products AS
SELECT TOP 5
[Link],
SUM([Link]) AS TotalSales
FROM [Link] s
JOIN [Link] p ON [Link] = [Link]
GROUP BY [Link]
ORDER BY TotalSales DESC;
Summary Table
View Name Purpose
vw_BasicCustomerInfo Basic customer data
vw_CustomerWithAddress Customer with address via JOIN
vw_TotalSalesByProduct Aggregated sales by product
vw_SafeCustomerView Security-focused view
vw_HighOrderCustomers Customers with >3 orders
vw_Top5Products Top selling products