Views trong SQL Server

1-- Basic view syntax
2CREATE VIEW ViewName
3AS
4SELECT columns
5FROM tables
6WHERE conditions;
7GO
8
9-- Example: View khách hàng active
10CREATE VIEW Customer.vw_ActiveCustomers
11AS
12SELECT 
13    CustomerID,
14    FirstName,
15    LastName,
16    Email,
17    Phone
18FROM Customer.Customer
19WHERE IsActive = 1;
20GO
21
22-- Use the view
23SELECT * FROM Customer.vw_ActiveCustomers;

1-- View ẩn complex JOINs
2CREATE VIEW Sales.vw_OrderSummary
3AS
4SELECT 
5    o.OrderID,
6    o.OrderNumber,
7    o.OrderDate,
8    c.FirstName + ' ' + c.LastName AS CustomerName,
9    c.Email AS CustomerEmail,
10    o.Status,
11    o.TotalAmount,
12    COUNT(od.OrderDetailID) AS ItemCount
13FROM Sales.[Order] o
14JOIN Customer.Customer c ON o.CustomerID = c.CustomerID
15LEFT JOIN Sales.OrderDetail od ON o.OrderID = od.OrderID
16GROUP BY 
17    o.OrderID, o.OrderNumber, o.OrderDate,
18    c.FirstName, c.LastName, c.Email,
19    o.Status, o.TotalAmount;
20GO
21
22-- Simple query từ view
23SELECT * 
24FROM Sales.vw_OrderSummary
25WHERE Status = 'Delivered'
26ORDER BY OrderDate DESC;

1-- AdventureWorks có nhiều Views sẵn
2-- Xem danh sách Views
3SELECT 
4    SCHEMA_NAME(schema_id) AS SchemaName,
5    name AS ViewName
6FROM sys.views
7ORDER BY SchemaName, ViewName;
8
9-- Ví dụ: Sales.vSalesPerson
10SELECT * FROM Sales.vSalesPerson;
11
12-- Ví dụ: HumanResources.vEmployee
13SELECT 
14    FirstName,
15    LastName,
16    JobTitle,
17    Department,
18    HireDate
19FROM HumanResources.vEmployee
20WHERE Department = 'Sales';

1-- SCHEMABINDING ngăn base tables bị modify/drop
2CREATE VIEW Sales.vw_ProductSales
3WITH SCHEMABINDING  -- Lock base tables
4AS
5SELECT 
6    p.ProductID,
7    p.ProductName,
8    SUM(od.Quantity) AS TotalQuantitySold,
9    SUM(od.LineTotal) AS TotalRevenue
10FROM Inventory.Product p
11JOIN Sales.OrderDetail od ON p.ProductID = od.ProductID
12GROUP BY p.ProductID, p.ProductName;
13GO
14
15-- Giờ không thể:
16-- DROP TABLE Inventory.Product  -- Error!
17-- ALTER TABLE Inventory.Product DROP COLUMN ProductName  -- Error!

1-- Ẩn view definition
2CREATE VIEW Sales.vw_SensitiveData
3WITH ENCRYPTION
4AS
5SELECT 
6    CustomerID,
7    Email,
8    Phone
9FROM Customer.Customer;
10GO
11
12-- Không thể xem definition
13SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.vw_SensitiveData'));
14-- Returns NULL

1-- Ngăn INSERT/UPDATE vi phạm WHERE clause
2CREATE VIEW Customer.vw_ActiveCustomers_Protected
3AS
4SELECT 
5    CustomerID,
6    FirstName,
7    LastName,
8    Email,
9    IsActive
10FROM Customer.Customer
11WHERE IsActive = 1
12WITH CHECK OPTION;
13GO
14
15-- Không thể insert inactive customer qua view này
16INSERT INTO Customer.vw_ActiveCustomers_Protected 
17    (FirstName, LastName, Email, IsActive)
18VALUES ('Test', 'User', 'test@email.com', 0);  -- Error!

1-- Indexed View lưu trữ data (materialized)
2-- Yêu cầu: SCHEMABINDING, deterministic functions
3
4CREATE VIEW Sales.vw_ProductSalesIndexed
5WITH SCHEMABINDING
6AS
7SELECT 
8    p.ProductID,
9    p.ProductName,
10    COUNT_BIG(*) AS SaleCount,
11    SUM(od.Quantity) AS TotalQuantity,
12    SUM(od.LineTotal) AS TotalRevenue
13FROM Inventory.Product p
14JOIN Sales.OrderDetail od ON p.ProductID = od.ProductID
15GROUP BY p.ProductID, p.ProductName;
16GO
17
18-- Tạo unique clustered index
19CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSalesIndexed
20ON Sales.vw_ProductSalesIndexed(ProductID);
21GO

1-- Indexed View tự động được dùng với Enterprise Edition
2-- Standard Edition cần hint
3SELECT * FROM Sales.vw_ProductSalesIndexed WITH (NOEXPAND);

1-- Updatable View
2CREATE VIEW Customer.vw_CustomerBasic
3AS
4SELECT 
5    CustomerID,
6    FirstName,
7    LastName,
8    Email,
9    Phone,
10    IsActive
11FROM Customer.Customer;
12GO
13
14-- Có thể UPDATE qua View
15UPDATE Customer.vw_CustomerBasic
16SET Phone = '0123456789'
17WHERE CustomerID = 1;
18
19-- Có thể INSERT qua View
20INSERT INTO Customer.vw_CustomerBasic (FirstName, LastName, Email)
21VALUES ('New', 'Customer', 'new@email.com');

1-- View với JOINs không updatable by default
2-- Dùng INSTEAD OF trigger để enable
3
4CREATE VIEW Sales.vw_OrderWithCustomer
5AS
6SELECT 
7    o.OrderID,
8    o.OrderNumber,
9    o.OrderDate,
10    o.CustomerID,
11    c.FirstName AS CustomerFirstName,
12    c.LastName AS CustomerLastName
13FROM Sales.[Order] o
14JOIN Customer.Customer c ON o.CustomerID = c.CustomerID;
15GO
16
17-- INSTEAD OF INSERT trigger
18CREATE TRIGGER trg_vw_OrderWithCustomer_Insert
19ON Sales.vw_OrderWithCustomer
20INSTEAD OF INSERT
21AS
22BEGIN
23    INSERT INTO Sales.[Order] (OrderNumber, OrderDate, CustomerID)
24    SELECT OrderNumber, OrderDate, CustomerID
25    FROM inserted;
26END;
27GO

1-- Modify existing view
2ALTER VIEW Customer.vw_ActiveCustomers
3AS
4SELECT 
5    CustomerID,
6    FirstName,
7    LastName,
8    Email,
9    Phone,
10    CreatedDate  -- Added column
11FROM Customer.Customer
12WHERE IsActive = 1;
13GO
14
15-- Best practice: CREATE OR ALTER (SQL Server 2016+)
16CREATE OR ALTER VIEW Customer.vw_ActiveCustomers
17AS
18SELECT 
19    CustomerID,
20    FirstName,
21    LastName,
22    Email,
23    Phone,
24    CreatedDate
25FROM Customer.Customer
26WHERE IsActive = 1;
27GO

1-- Drop single view
2DROP VIEW Customer.vw_ActiveCustomers;
3
4-- Drop if exists
5DROP VIEW IF EXISTS Customer.vw_ActiveCustomers;
6
7-- Drop multiple views
8DROP VIEW 
9    Customer.vw_View1,
10    Customer.vw_View2;

1-- Prefix với vw_ hoặc V_
2vw_ActiveCustomers
3vw_OrderSummary
4vw_ProductSales
5
6-- Hoặc không prefix nhưng dùng schema
7Reporting.CustomerSummary
8Reporting.SalesAnalysis

1-- ❌ KHÔNG nên: Nested views (view gọi view)
2CREATE VIEW vw_Level1 AS SELECT * FROM Table1;
3CREATE VIEW vw_Level2 AS SELECT * FROM vw_Level1;
4CREATE VIEW vw_Level3 AS SELECT * FROM vw_Level2;
5-- Query optimizer khó optimize
6
7-- ✅ NÊN: Flat views
8CREATE VIEW vw_Final AS 
9SELECT * FROM Table1
10JOIN Table2...
11
12-- ❌ KHÔNG nên: SELECT *
13CREATE VIEW vw_Bad AS SELECT * FROM BigTable;
14
15-- ✅ NÊN: Explicit columns
16CREATE VIEW vw_Good AS 
17SELECT Col1, Col2, Col3 FROM BigTable;

1-- Row-level security
2CREATE VIEW HR.vw_MyDepartmentEmployees
3AS
4SELECT 
5    EmployeeID,
6    FirstName,
7    LastName,
8    Salary
9FROM HR.Employee
10WHERE DepartmentID = (
11    SELECT DepartmentID 
12    FROM HR.Employee 
13    WHERE Email = SUSER_SNAME()
14);
15GO
16
17-- Column-level security (hide sensitive data)
18CREATE VIEW Customer.vw_CustomerPublic
19AS
20SELECT 
21    CustomerID,
22    FirstName,
23    LastName,
24    -- Email masked
25    LEFT(Email, 3) + '***@***' AS MaskedEmail,
26    -- Phone hidden
27    'XXX-XXX-' + RIGHT(Phone, 4) AS MaskedPhone
28FROM Customer.Customer;
29GO

1-- Xem definition của View
2SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.vSalesPerson')) AS ViewDefinition;
3
4-- Hoặc dùng sp_helptext
5EXEC sp_helptext 'Sales.vSalesPerson';
6
7-- Xem View với dependencies
8SELECT 
9    v.name AS ViewName,
10    OBJECT_NAME(d.referenced_id) AS DependsOn,
11    d.referenced_entity_name
12FROM sys.views v
13JOIN sys.sql_expression_dependencies d ON v.object_id = d.referencing_id
14WHERE SCHEMA_NAME(v.schema_id) = 'Sales';

1-- HumanResources.vEmployee - Employee info với Person data
2SELECT TOP 5 * FROM HumanResources.vEmployee;
3
4-- Sales.vSalesPerson - Sales person với quota và YTD sales
5SELECT TOP 5 * FROM Sales.vSalesPerson;
6
7-- Production.vProductAndDescription - Product với description
8SELECT TOP 5 * FROM Production.vProductAndDescription;
9
10-- Sales.vIndividualCustomer - Customer info
11SELECT TOP 5 * FROM Sales.vIndividualCustomer;

1-- 1. View: Top Products by Revenue
2CREATE OR ALTER VIEW Reporting.vw_TopProductsByRevenue
3AS
4SELECT TOP 100
5    p.ProductID,
6    p.Name AS ProductName,
7    pc.Name AS Category,
8    SUM(sod.OrderQty) AS TotalQuantitySold,
9    SUM(sod.LineTotal) AS TotalRevenue,
10    AVG(sod.UnitPrice) AS AvgSellingPrice
11FROM Production.Product p
12JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
13JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
14JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
15GROUP BY p.ProductID, p.Name, pc.Name
16ORDER BY TotalRevenue DESC;
17GO
18
19-- 2. View: Monthly Sales Summary
20CREATE OR ALTER VIEW Reporting.vw_MonthlySalesSummary
21AS
22SELECT 
23    YEAR(soh.OrderDate) AS SalesYear,
24    MONTH(soh.OrderDate) AS SalesMonth,
25    COUNT(DISTINCT soh.SalesOrderID) AS OrderCount,
26    COUNT(DISTINCT soh.CustomerID) AS CustomerCount,
27    SUM(soh.SubTotal) AS TotalSubTotal,
28    SUM(soh.TaxAmt) AS TotalTax,
29    SUM(soh.Freight) AS TotalFreight,
30    SUM(soh.TotalDue) AS TotalRevenue
31FROM Sales.SalesOrderHeader soh
32GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate);
33GO
34
35-- 3. View: Customer Purchase History
36CREATE OR ALTER VIEW Customer.vw_CustomerPurchaseHistory
37AS
38SELECT 
39    c.CustomerID,
40    p.FirstName + ' ' + p.LastName AS CustomerName,
41    COALESCE(p.EmailPromotion, 0) AS EmailPromotion,
42    COUNT(DISTINCT soh.SalesOrderID) AS TotalOrders,
43    MIN(soh.OrderDate) AS FirstOrderDate,
44    MAX(soh.OrderDate) AS LastOrderDate,
45    SUM(soh.TotalDue) AS LifetimeValue,
46    AVG(soh.TotalDue) AS AvgOrderValue
47FROM Sales.Customer c
48LEFT JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
49LEFT JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
50GROUP BY c.CustomerID, p.FirstName, p.LastName, p.EmailPromotion;
51GO
52
53-- Test views
54SELECT * FROM Reporting.vw_TopProductsByRevenue;
55SELECT * FROM Reporting.vw_MonthlySalesSummary ORDER BY SalesYear DESC, SalesMonth DESC;
56SELECT TOP 10 * FROM Customer.vw_CustomerPurchaseHistory ORDER BY LifetimeValue DESC;