Khái niệm view

View là một bảng ảo được tạo ra bằng các cột được lựa chọn trong một hoặc nhiều bảng. Bảng trong view được tạo tham chiếu tới bảng gốc. Các bảng gốc này có thể từ các database khác nhau. Một view cũng có thể thêm một cột vào từ một view khác đợc tạo trong cùng database hoặc thậm chí khác database. Một view có tể có tối đa 1024 cột. Dữ liệu bên trong view lấy ra từ bảng gốc sẽ tham chiếu tới việc định nghĩa view.Hàng và cột tỏng view sẽ được tạo động khi view được tham chiếy

Một số tình huống có thể sử dụng view làm nghiệp vụ:

• Cần lấy tổng doanh thu đơn hàng mỗi ngày ( chỉ lấy cột doanh thu) đáp ứng nghiệp vụ báo cáo
• Cần làm một bảng hiển thị một số cột để đưa ra dữ liệu cho dev mà không cho dev truy vấn trực tiếp vào CSDL để có thể nhìn được các cột còn lại, và bảng này chỉ cho xem dữ liệu chữ không cho cập nhật hay thêm mới.

Với những tình huống như vậy, như tình huống số 1, người quản trị CSDL (DBA) sẽ sử dụng view để làm nghiệp vụ báo cáo thay cho việc viết một câu lệnh select phức tạp nhiều lần, hay tình huống số 2, view sẽ đảm bảo cả về góc độ bảo mật lẫn nghiệp vụ cho DBA.

Create View

Người dùng có thẻ tạo view bằng sử dụng cột từ bảng hoặc view khác chỉ khi người dùng được cấp quyền truy cập vào các bảng và views này.

Cú pháp:

CREATE VIEW <view_name>
AS <select_statement>

Ví dụ:

Tạo một view từ bảng Production.Product để hiển thị productid, productnumber,name, safety stock của sản phẩm:

CREATE VIEW vwProductInfo AS
SELECt ProductID, ProductNumber,Name,SafetyStockLevel
FROM Production.Product;
GO

Lấy dữ liệu từ view:

SELECT * FROM vwProductInfo

Tạo View kết hợp mệnh đề JOIN để nối bảng

Mỗi một view có thể lưu trữ dữ liệu của nhiều table khác nhau vì nó là kết quả của câu truy vấn SELECT, đây chính là ưu điểm khiến view trở nên mạnh mẽ.

Từ khóa JOIN có thể được sử dụng trong khi tạo view, về cơ bản đaya chỉ là phép nối để nối các bảng lại với nhau thông qua các cột liên quan, sau khi nối, view được tạo dựa trên dữ liệu lấy ra sau phép nối.

Cú pháp:

CREATE VIEW <view_name> AS
SELECT * FROM table_name1 JOIN
table_name2 ON table_name1.column_name = table_name2.column_name

Ví dụ:

Tạo một view có tên vwPersonDetails với các cột xác định từ bảng Person và Employee trong HumanResources schema. Từ khóa JOIN và ON được sử dụng để nối 2 bảng dựa trên cột BusinessEntityID

CREATE VIEW vwPersonDetails AS
SELECT p.Title, p.[FirstName], p.[MiddleName], p.[LastName], e.[JobTitle]
FROM [humanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID];
GO
SELECT * FROM vwPersonDetails

Ở ví dụ phái trên, có nhiều rows không có giá trị ở cột title và cột name, và hiển thị ra giá trị null. Có thể, khi người dùng nhìn thấy đầu ra này có thể không hiểu được ý nghĩa của các giá trị NULL. Do đó, để thay thế tất cả các giá trị NULL trong đầu ra bằng một chuỗi rỗng, có thể sử dụng hàm COALESCE()

CREATE VIEW vwPersonDetails AS
SELECT COALESCE(p.Title,'') AS Title, p.[FirstName], COALESCE(p.[MiddleName],'') AS MiddleName, 
p.[LastName], e.[JobTitle]
FROM [humanResources].[Employee] e INNER JOIN [Person].[Person] p 
ON p.[BusinessEntityID] = e.[BusinessEntityID];
GO
SELECT * FROM vwPersonDetails

Một số luật cần tuân thủ khi sử dụng view:

• View chỉ có thể được tạo trên database hiện thời. Chỉ sau khi view đã được tạo, bảng và view dựa trên view đó có thể tạo từ database hoặc server khác.
• View name luôn phải duy nhất và không thể cùng tên với bảng trên schema.
• View không thể tạo từ bảng tạm (temporary tables).

tạo view kết hợp với Order by để sắp xếp

Ví dụ dưới sử dụng mệnh đề TOP kết hợp với ORDER BY để lấy ra 10 nhân sự theo chiều ngược dựa theo cột FirstName:

CREATE VIEW vwSortedPersonDetails AS
SELECT TOP 10 COALESCE (p.title,' ') AS Title,
p.[FirstName]
,COALESCE(p.MiddleName,' ') AS MiddleName
,p.[LastName]
,e.[JobTitle]
FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID] ORDER BY p.FirstName DESC
GO
SELECT * FROM vwSortedPersonDetails 

Chỉnh sử dữ liệu thông qua View

Views có thể sử dụng để chỉnh sửa dữ liệu trong bảng, dữ liệu có thể được thêm vào, chỉnh sửa hoặc deleted thông qua các câu lệnh:

• INSERT
• UPDATE
• DELETE

INSERT view

Câu lệnh INSERT sử dụng để thêm hàng mới cho database hoặc view, trong khi thực thi câu lệnh, nếu như giá trị của cột không được cung cấp, SQL Server Database Engine sẽ phải cung cấp giá trị dựa theo định nghĩa của cột. Nếu Database Engine không cung cấp được giá trị này, hàng mới sẽ không được thêm.

Các giá trị cho cột được cung cấp giá trị tự động:

• Có thuộc tính IDENTITY
• Có xác định giá trị mặc định
• Kiểu dữ liệu timestamp
• Cột cho phép giá trị null
• Cột có giá trị được tính toán

Khi sử dụng câu lệnh INSERT trong view, nếu có luật nào vi phạm, bản ghi sẽ không được thêm.

Ví dụ:

Bước 1: tạo bảng Employee_Personal_details

CREATE TABLE Employee_Personal_Details(
EmpID int NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Address varchar(30)
)

Bước 2: Tạo bảng Employee_Salary_Details

CREATE TABLE Employee_Salary_Details(
EmpID int not null,
Designation varchar(30),
Salary int not null
)

Bước 3: Tạo view vwEmployee_Details sử dụng cột từ bảng Employee_Personal_Details và Employee_Salary_Details, bằng cách nối 2 bảng thông qua cột EmpID

CREATE VIEW vwEmployee_Personal_Details
AS 
SELECT e1.EmpID, FirstName, LastName, Designation, Salary
FROM Employee_Personal_Details e1
JOIN Employee_Salary_Details e2
ON e1.EmpID = e2.EmpID

Sử dụng câu lệnh INSERT để đưa data vào bảng thông qua view vwEmployee_Details. Tuy nhiên, SQL thông báo lỗi và dữ liệu không đưa được vào bảng:

INSERT INTO vwEmployee_Personal_Details VALUES(2,'Jack','Wilson','Software Developer',16000)

Giá trị chỉ có thể thêm vào các cột có kiểu dữ liệu người dùng đinh nghĩa trước như sau:

• Xác định giá trị của kiểu dữ liệu người dùng đã định nghĩa
• Gọi một hàm người dùng định nghĩa để trả về giá trị với kiểu dữ liệu người dùng tự định nghĩa

Một số quy luật cần tuân thủ khi insert dữ liệu vào view:

• Câu lệnh INSERT cần xác định giá trị cho toàn bộ cột trong view trong bảng nếu bảng không cho phép giá trị rỗng và không có định nghĩa DEFAULT
• Khi có phép self-join ở trong cùng một view hoặc table, câu lệnh INSERT sẽ không hoạt động

Ví dụ tạo một view có tên vwEmpdetails sử dụng bảng Employee_Personal_Details. Bảng Employee_Personal_Details chứa cột LastName không cho phép insert giá trị null:

Ví dụ trên khi insert sẽ báo lỗi vì không cho phép insert giá trị null cho cột EmpID

CREATE VIEW vwEmpDetails AS
SELECT FirstName, Address
FROM Employee_Personal_Details
GO
INSERT INTO vwEmpDetails VALUES('Jack','NYC')

Update View

Câu lệnh UPDATE có thể sử dụng để thay đổi data trong view. Cập nhật view cũng sẽ cập nhật dữ liệu của bảng liên quan.

Ví dụ:

Bước 1: tạo bảng có tên Product_Details

CREATE TABLE Product_Details(
ProductID int,
ProductName varchar(30),
Rate money
)
GO
INSERT INTO Product_Details VALUES (1,'DVD Writer',1250), (2,'DVD Writer',2250), (3,'DVD Writer',1250),(4,'External Hard Drive',2250), (5,'External Hard Drive',2250), (6,'External Hard Drive',2250);
GO

Giả sử thêm một số giá trị vào bảng như hình:

Bước 2: tạo view dựa trên bảng ProductDetails

CREATE VIEW vwProduct_Details
AS 
SELECT ProductName,Rate FROM Product_Details

Bước 3: cập nhật view để thay đổi tooàn bộ giá trị DVD thành 4000

UPDATE vwProduct_Details
SET Rate=3000
WHERE ProductName='DVD Writer'

Những kiểu dữ liệu chiếm kích thước vùng nhớ lớn như nvarchar(max), varchar(max), varbinary(max). Để cập nhật dữ liệu kiểu này, mệnh đề .WRITE được sử dụng. Mệnh đề .WRITE xác định phân vùng mà giá trị của cột sẽ được chỉnh suawr. Mệnh đề .WRITE không thể sử dụng để cập nhật giá trị NULL cho cột. Vì vậy, không thể sử dụng để gián giá trị cho cột thành NULL

Cú pháp:

column_name .WRITE(expression,@Offeset,@Length)

Giả sử rằng bảng Product_Details được chỉnh sửa để cột Description có dữ liệu kiểu nvarchar(max)

View được tạo dựa trên bảng này, có các cột ProductName,Description, Rate

CREATE VIEW vwProduct_Details AS
SELECT ProductName, Description, Rate FROM Product_Details

Ví dụ code trên sử dụng lệnh UPDATE cho view vwProduct_Details. Mệnh đề .WRITE sẽ được sử dụng với 2 tham số 0 và 2 để thay đổi giá trị 2 ký tự đầu của cột Description. Khi đó giá trị ‘Internal’ sẽ thay đổi thành ‘External’

UPDATE vwProduct_Details
SET Description .WRITE(N'Ex',0,2)
WHERE ProductName='PortableHardDrive'

Một số quy luật cần tuân thủ khi sử dụng khi sử dụng câu lệnh UPDATE trong view:

• Giá trị của cột với thuộc tính IDENTITY không thể update.
• Bản ghi không thể đưojc cập nhật nếu bảng chứa cột có giá trị TIMESTAMP
• Khi có self-join trong cùng view hoặc bảng, caua lệnh UPDATE không hoạt động.
• Trong khi update hàng, nếu ràng buộc bị vi phạm, câu lệnh sẽ chấm dữ và lỗi trả ra, không có bản ghi nào được update

DELETE View

SQL Server cho phép người dùng có thể xóa bản ghi từ view. Hàng có thể được xóa khỏi VIEW bằng câu lệnh DELETE. Khi bản ghi được xóa khỏi view, các hàng tương tứng sẽ được xóa khỏi bảng

Ví dụ, có view vwCustDetails, sẽ lấy ra toàn bộ các thông tin tài khoản từ khác customer khác nhau. Khi customer đóng tài khoản, các thông tin chi tiết của customer cần được xóa.

Cú pháp:

DELETE FROM <view_name>
WHERE <search_condition>

Ví dụ:

DELETE FROM vwCustDetails WHERE CustID='C0004'

Alter View

Bên cạnh việc chỉnh sửa dữ liệu, ngời dùng cũng có thể chỉnh sửa các định nghĩa bên trong view. Một view có thể được chỉnh suawr thông qua lệnh ALTER VIEW. Lệnh ALTER VIEW chỉnh sửa view hiện tại mà không cần phải tổ chức lại quyền hoặc các thuộc tính khác.

ALTER VIEW có thể được áp dụng cho các indexed view, tuy nhiên, nó sẽ xóa tất cả các index của view một cách vô điều kiện. View thường bị thay đổi khi người dùng yêu cầu thông tin bổ sung hoặc thực hiện thay đổi trong định nghĩa bảng cơ bản

ALTER VIEW <view_name>
AS <select_statement>

Ví dụ:

ALTER VIEW vwProductInfo AS
SELECT ProductID, ProductNumber,Name, SafetyStockLevel,ReOrderPoint
FROM Production.Product;
GO

DROP View

Một view có thể được xóa khỏi database nếu không cần sử dụng, nó sử dụng thông qua câu lệnh DROP VIEW. Một khi view được xóa, dữ liệu ở trong bảng chính không bị ảnh hưởng. Định nghĩa của view và các thông tin liên quan tới view sẽ được xóa khỏi danh mục hệ thống. Tất cả quyền của view cũng sẽ được xóa. Nếu người dùng truy vấn tới view tham chiếu tới view đã bị xóa, sql server sẽ trả ra thông báo lỗi.

Cú pháp:

DROP VIEW <view_name>

Ví dụ:

DROP VIEW vwProductInfo

View Definition

Định nghĩa của một view giúp người dùng hiểu cách dữ liệu của nó được lấy từ các bảng nguồn. Có một số thủ tục được lưu trữ (stored procedure) trong hệ thống giúp truy xuất các định nghĩa về view. Thủ tục lưu trữ sp_helptext hiển thị thông tin liên quan đến view khi tên của view được cung cấp làm tham số của nó. Thông tin về định nghĩa của một view có thể được lấy nếu thông tin đó không được mã hóa.

sp_helptext<view_name>

Ví dụ:

exec sp_helptext vwEmployee_Personal_Details

CHECK OPTION View

Từ khóa CHECK OPTION của view là tùy chọn, đây là một tùy chọn liên kết với câu lệnh CREATE VIEW. Nó được sử dụng để đảm bảo rằng tất cả các cập nhật ở view thỏa mãn các điều kiện được đề cập ở định nghĩa view. Nếu điều kiện không thỏa mãn, database engine sẽ trả ra lỗi. Vì vậy, CHECK OPTION sử dụng để đảm bảo tính logic, toàn vẹn cho dữ liệu, nó sẽ kiểm tra định nghĩa của view để xem các điều kiện WHERE trong câu lệnh SELECT có vi phạm hay không.

Với mệnh đề WITH CHECK OPTION, buộc tất cả các trạng thái sửa đổi được thực thi đối với view phải tuân theo điều kiện được đặt trong câu lệnh SELECT.

Cú pháp:

CREATE VIEW <view_name>
AS select_statement [WITH CHECK OPTION]

Ví dụ tạo lại view vwProductInfo có SafetyStockLevel nhỏ hơn hoặc bằng 1000:

CREATE VIEW vwProductInfo AS
SELECT ProductID, ProductNumber, Name, SafetyStockLevel, ReOrderPoint FROM
Production.Product
WHERE SafetyStockLevel <= 1000
WITH CHECK OPTION;
GO

Bước tiếp theo, dùng câu lệnh UPDATE sử dụng để chỉnh sửa view vwProductInfo bằng cách đổi giá trị cột SafetyStockLevel cho sản phẩm có id là 321 và 2500

UPDATE vwProductInfo SET SafetyStockLevel = 2500
WHERE ProductID = 321

Câu lệnh này sẽ không được thực thi vì vi phạm ràng buộc WITH CHECK OPTION, SafetyStockLevel <= 1000. Vì vậy, không có row nào được cập nhật trong view vwProductInfo.

SCHEMA BINDING OPTION trong View

Một view có thể được liên kết với lược đồ của bảng bằng cách sử dụng tùy chọn SCHEMABINDING. Tùy chọn này có thể được sử dụng với các câu lệnh CREATE VIEW hoặc ALTER VIEW. Khi SCHEMABIDING được sử dụng, bảng cơ sở hoặc các bảng không thể được sửa đổi sẽ ảnh hưởng đến định nghĩa view. Trước tiên phải sửa đổi hoặc xóa view để loại bỏ các phần phụ thuộc vào bảng sẽ được sửa đổi.

Khi sử dụng SCHEMABINDING trong view, phải xác định schema name cùng với object name trong câu lệnh SELECT

Cú pháp:

CREATE VIEW <view_name> WITH SCHEMABINDING
AS <select_statement>

Ví dụ:

CREATE VIEW vwNewProductInfo WITH SCHEMABINDING AS 
SELECT ProductID,ProductNumber,Name,SafetyStockLevel
FROM Production.Product;
GO

Sử dụng sp_refreshview

Trong quá trình tạo view, SCHEMABIDING có thể được sử dụng để liên kết giá trị từ view vào schema của bảng được thêm vào trong view. Tuy nhiên, view có thể tạo mà không cần lựa chọn SCHEMABIDING option. Trong các tình huốngn ày, nếu thay đổi diễn ra trên đối tượng như bảng hoặc view mà view này phụ thuộc vào, stored procedure có thên sp_refreshview sẽ nên được thựuc thi. Stored procedure này cập nhật metadata cho view, nếu nó không được thực thi, metadata của view sẽ không được cập nhật theo thay đổi của bảng gốc. Tập kết quả sẽ trả ra một số kết quả không mong muốn khi view được yêu cầu.

Cú pháp:

sp_refreshview '<view_name>'

Ví dụ:

Bước 1 – tạo bảng Customers

CREATE TABLE Customers(
CustID int,
CustName varchar(50),
Address varchar(60)
)

Bước 2 – tạo view vwCustomers dựa trên bảng Customers.

CREATE VIEW vwCustomers
AS
SELECT * FROM Customers

Bước 3 – select dữ liệu trong view

SELECT * FROM vwCustomers

Đoạn output trên sẽ trả ra 3 cột CustID,CustName,Address

Bước 4 – ALTER TABLE và bổ sung thêm cột Age trong bảng Customers.

ALTER TABLE Customers ADD Age int

Bước 5 – select lại dữ liệu trong view, tuy nhiên bạn sẽ thấy cột Age chưa được hiển thị

SELECT * FROM vwCustomer

Bước 6 – Thực thi store procedure sp_refreshview để làm mới metadata và output của view:

EXEC sp_refreshview 'vwCustomers'

Bảng mà có kết nối schema với view không thể drop trừ khi view drop trước hoặc thay đổi định nghĩa thành không có schemabiding. Nếu view chưa được xóa hoặc cập nhật mà bạn cố gắng xóa bảng, Database Engine sẽ trả về thông báo lỗi.

Cũng như vậy, khi một câu lệnh ALTER TABLE áp dụng vào định nghĩa view schema-bound, câu lệnh này sẽ thất bại.

Ví dụ:

ALTER TABLE Production.Product ALTER COLUMN ProductID varchar(7)

Database Engine sẽ trả về lỗi vì bảng này có schema-bound với view vwNewProductInfo, và vì vậy việc sửa đổi vi phạm định nghĩa của view.

Các tính năng của View

Nói chung khi bạn sử dụng View thì sẽ có những lợi thế sau:

Về bảo mật

Bạn có thể hạn chế người dùng truy cập trực tiếp vào table, thay vào đó cho họ truy cập thông qua view nên sẽ an toàn hơn. Tại sao? Tại vì view chỉ là một khung nhìn nên chỉ cho phép đọc được thông tin tồn trại trong view, không được xem thêm thông tin hoặc thay đổi dữ liệu.

Ví dụ bạn có thể cho phép người dùng truy cập tên khách hàng, điện thoại, email thông qua chế độ xem nhưng hạn chế họ truy cập vào tài khoản ngân hàng và các thông tin nhạy cảm khác.

Đơn giản hóa

Khi viết câu truy vấn với nhiều bảng thì sẽ rất phức tạp, ban phải test nhiều thì mới chắc chắn là ra kết quả đúng, cũng có khi chưa chắc đúng. Tuy nhiên nếu kết hợp view thì bạn phân ra thành nhiều phân đoạn và mỗi view là một phân đoạn, sao đó thực hiện các phép truy vấn trên view thì sẽ dễ hiểu hơn nhiều.

Tính nhất quán

Đôi khi bạn cần viết một công thức phức tạp và được sử dụng trong nhiều câu truy vấn, lúc này bạn sẽ phải viết đi viết lại nhiều lần.

Nhưng nếu đưa công thức đó vào một view, sau đó các câu truy vấn kia tham chiếu từ view thì sẽ tiện lợi và nhất quán, sau này chỉnh sửa công thức thì chỉ cần sửa ở view.