View trong SQL Server

Trong hướng dẫn này, bạn sẽ tìm hiểu về cách quản lý view như tạo view mới, xóa view và cập nhật dữ liệu của các bảng bên dưới thông qua view.

Khi bạn sử dụng câu lệnh SELECT để truy vấn dữ liệu từ một hoặc nhiều bảng, bạn sẽ nhận được một tập kết quả.

Ví dụ: câu lệnh sau đây trả về tên sản phẩm, nhãn hiệu và giá niêm yết của tất cả các sản phẩm từ bảng products và bảng brands:

SELECT
    product_name, 
    brand_name, 
    list_price
FROM
    production.products p
INNER JOIN production.brands b 
    ON b.brand_id = p.brand_id;

Lần tới, nếu bạn muốn có được tập kết quả tương tự, bạn có thể lưu truy vấn này vào một file, mở nó và thực hiện lại.

SQL Server cung cấp một cách tốt hơn để lưu truy vấn này trong cơ sở dữ liệu thông qua view.

View là một truy vấn được đặt tên và được lưu trữ trong cơ sở dữ liệu cho phép bạn sử dụng nó sau này.

Vì vậy, truy vấn trên có thể được lưu trữ dưới dạng dạng view bằng cách sử dụng câu lệnh CREATE VIEW như sau:

CREATE VIEW sales.product_info
AS
SELECT
    product_name, 
    brand_name, 
    list_price
FROM
    production.products p
INNER JOIN production.brands b 
    ON b.brand_id = p.brand_id;

Sau đó, bạn có thể tham chiếu đến view trong câu lệnh SELECT như sau:

SELECT * FROM sales.product_info;

Khi nhận được truy vấn này, SQL Server sẽ thực hiện truy vấn sau:

SELECT 
    *
FROM (
    SELECT
        product_name, 
        brand_name, 
        list_price
    FROM
        production.products p
    INNER JOIN production.brands b 
        ON b.brand_id = p.brand_id;
);

Theo định nghĩa, view không lưu trữ dữ liệu ngoại trừ các view được lập chỉ mục .

View có thể chứa các cột từ nhiều bảng bằng cách sử dụng các mệnh đề join hoặc chỉ là một tập hợp con các cột của một bảng. Điều này làm cho view hữu ích trong việc trừu tượng hóa hoặc ẩn các truy vấn phức tạp.

Hình ảnh sau đây minh họa view bao gồm các cột từ nhiều bảng:

Ưu điểm của View trong SQL Server

Nói chung, view cung cấp các ưu điểm sau:

Bảo mật

Bạn có thể hạn chế người dùng truy cập trực tiếp vào bảng và cho phép họ truy cập tập hợp con dữ liệu thông qua view.

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 qua view 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.

Sự đơn giản

Một cơ sở dữ liệu quan hệ có thể có nhiều bảng với các mối quan hệ phức tạp, ví dụ: một-một, một-nhiều và nhiều-nhiều gây khó khăn cho việc điều hướng.

Tuy nhiên, bạn có thể đơn giản hóa các truy vấn phức tạp với các mệnh đề join và điều kiện bằng cách sử dụng view.

Tính nhất quán

Đôi khi, bạn cần viết một công thức hoặc logic phức tạp trong mọi truy vấn.

Để làm cho nó nhất quán, bạn có thể ẩn logic và các phép tính phức tạp vào trong view.

Khi view được định nghĩa, bạn có thể tham chiếu logic từ view thay vì viết lại nó trong các truy vấn riêng biệt.

Quản lý view trong SQL Server

• Tạo view mới - hướng dẫn bạn cách tạo view mới trong cơ sở dữ liệu SQL Server.
• Đổi tên view - tìm hiểu cách đổi tên view bằng SQL Server Management Studio (SSMS) hoặc lệnh Transact-SQL.
• Liệt kê view trong SQL Server - thảo luận về nhiều cách khác nhau để liệt kê tất cả các view trong cơ sở dữ liệu SQL Server.
• Lấy thông tin view - cách lấy thông tin về view.
• Xóa view - hướng dẫn bạn cách sử dụng câu lệnh DROP VIEW để xóa một hoặc nhiều view khỏi cơ sở dữ liệu.
• Tạo view được lập chỉ mục - hướng dẫn bạn cách tạo view được lập chỉ mục đối với các bảng không thường xuyên cập nhật dữ liệu để tối ưu hóa hiệu suất của view.

Tạo view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh CREATE VIEW trong SQL Server để tạo view mới.

Để tạo view mới trong SQL Server, bạn sử dụng câu lệnh CREATE VIEW như dưới đây:

CREATE [OR ALTER] VIEW schema_name.view_name [(column_list)]
AS
    select_statement;

Trong cú pháp này:

• Đầu tiên, chỉ định tên của view sau từ khóa CREATE VIEW. schema_name là tên của lược đồ mà view thuộc về.
• Thứ hai, chỉ định một câu lệnh SELECT (select_statement) để định nghĩa view sau từ khóa AS. Câu lệnh SELECT có thể truy vấn một hoặc nhiều bảng.

Nếu bạn không chỉ định rõ ràng danh sách các cột cho view, SQL Server sẽ sử dụng danh sách cột từ câu lệnh SELECT.

Trong trường hợp bạn muốn định nghĩa lại view, ví dụ: thêm nhiều cột vào nó hoặc xóa một số cột khỏi nó, bạn có thể sử dụng câu lệnh ALTER VIEW.

Chúng tôi sẽ sử dụng các bảng orders, order_items và products từ các cơ sở dữ liệu mẫu để minh họa tạo view trong SQL Server.

Tạo một view đơn giản trong SQL Server

Câu lệnh sau tạo một view tên daily_sales dựa trên các bảng orders, order_items và products:

CREATE VIEW sales.daily_sales
AS
SELECT
    year(order_date) AS y,
    month(order_date) AS m,
    day(order_date) AS d,
    p.product_id,
    product_name,
    quantity * i.list_price AS sales
FROM
    sales.orders AS o
INNER JOIN sales.order_items AS i
    ON o.order_id = i.order_id
INNER JOIN production.products AS p
    ON p.product_id = i.product_id;

Khi view daily_sales được tạo, bạn có thể truy vấn dữ liệu từ các bảng trên bằng cách sử dụng câu lệnh SELECT đơn giản như sau:

SELECT 
    * 
FROM 
    sales.daily_sales
ORDER BY
    y, m, d, product_name;

Sau đây cho thấy đầu ra:

Định nghĩa lại view trong SQL Server

Để thêm cột tên khách hàng vào view sales.daily_sales, bạn sử dụng câu lệnh ALTER VIEW như sau:

ALTER VIEW sales.daily_sales
AS
SELECT
    year(order_date) as y,
    month(order_date) as m,
    day(order_date) as d,
    concat(
        first_name,
        ' ',
        last_name
    ) as customer_name,
    p.product_id,
    product_name,
    quantity * i.list_price as sales
FROM
    sales.orders AS o
    INNER JOIN
        sales.order_items AS i
    ON o.order_id = i.order_id
    INNER JOIN
        production.products AS p
    ON p.product_id = i.product_id
    INNER JOIN sales.customers AS c
    ON c.customer_id = o.customer_id;

Câu lệnh sau truy vấn dữ liệu sử dụng view sales.daily_sales:

SELECT 
    * 
FROM 
    sales.daily_sales
ORDER BY 
    y, 
    m, 
    d, 
    customer_name;

Sau đây cho thấy đầu ra:

Tạo view sử dụng hàm tập hợp trong SQL Server

Câu lệnh sau đây tạo ra một view có tên là staff_sales để thống kê doanh số của nhân viên theo năm sử dụng hàm SUM():

CREATE VIEW sales.staff_sales (
        first_name, 
        last_name,
        year, 
        amount
)
AS 
    SELECT 
        first_name,
        last_name,
        YEAR(order_date),
        SUM(list_price * quantity) amount
    FROM
        sales.order_items i
    INNER JOIN sales.orders o
        ON i.order_id = o.order_id
    INNER JOIN sales.staffs s
        ON s.staff_id = o.staff_id
    GROUP BY 
        first_name, 
        last_name, 
        YEAR(order_date);

Câu lệnh sau truy vấn dữ liệu sử dụng view sales.staff_sales:

SELECT  
    * 
FROM 
    sales.staff_sales
ORDER BY 
	first_name,
	last_name,
	year;

Sau đây cho thấy đầu ra:

Xóa view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh DROP VIEW trong SQL Server để xóa view.

Để xóa view khỏi cơ sở dữ liệu, bạn sử dụng câu lệnh DROP VIEW như sau:

DROP VIEW [IF EXISTS] schema_name.view_name;

Trong cú pháp này, bạn chỉ định tên của view mà bạn muốn xóa sau từ khóa DROP VIEW. Nếu view thuộc về một lược đồ, bạn cũng phải chỉ định rõ ràng tên của lược đồ mà view thuộc về.

Nếu bạn cố xóa view không tồn tại, SQL Server sẽ báo lỗi. Từ khóa IF EXISTS ngăn chặn một lỗi xảy ra khi bạn xóa view không tồn tại.

Để xóa nhiều view, bạn sử dụng cú pháp sau:

DROP VIEW [IF EXISTS] 
    schema_name.view_name1, 
    schema_name.view_name2,
    ...;

Trong cú pháp này, các view được phân tách bằng dấu phẩy.

Lưu ý: khi bạn xóa view, SQL Server sẽ xóa tất cả các quyền cho view.

Chúng tôi sẽ sử dụng view sales.daily_sales và sales.staff_sales được tạo trong phần hướng dẫn tạo view để minh họa xóa view.

Xóa một view trong SQL Server

Ví dụ sau đây minh họa cách xóa view sales.daily_sales từ cơ sở dữ liệu:

DROP VIEW IF EXISTS sales.daily_sales;

Xóa nhiều view trong SQL Server

Câu lệnh sau đây tạo view product_catalogs cho mục đích minh họa xóa nhiều view trong SQL Server:

CREATE VIEW sales.product_catalog
AS
SELECT 
    product_name, 
    category_name, 
    brand_name,
    list_price
FROM 
    production.products p
INNER JOIN production.categories c 
    ON c.category_id = p.category_id
INNER JOIN production.brands b
    ON b.brand_id = p.brand_id;

Câu lệnh sau đây xóa cả hai view sales.staff_sales và sales.product_catalog cùng một lúc:

DROP VIEW IF EXISTS 
    sales.staff_sales, 
    sales.product_catalogs;

Đổi tên view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách đổi tên view trong cơ sở dữ liệu SQL Server.

Trước khi đổi tên view, bạn phải lưu ý rằng tất cả các đối tượng phụ thuộc vào view có thể gặp lỗi. Chúng bao gồm stored procedure, hàm người dùng tự định nghĩa, trigger, truy vấn, các view khác và các ứng dụng khách.

Do đó, sau khi đổi tên view, bạn phải đảm bảo rằng tất cả các đối tượng tham chiếu đến tên cũ của view đều sử dụng tên mới.

Đổi tên view bằng Server Server Management Studio (SSMS)

Để đổi tên tên của view, bạn làm theo các bước sau:

Đầu tiên, trong Object Explorer, mở rộng Database, chọn tên cơ sở dữ liệu chứa view mà bạn muốn đổi tên và mở rộng thư mục Views.

Thứ hai, nhấp chuột phải vào view mà bạn muốn đổi tên và chọn Rename.

Thứ ba, nhập tên mới cho view.

Đổi tên view bằng câu lệnh Transact-SQL

Nếu bạn muốn đổi tên view bằng câu lệnh, bạn có thể sử dụng stored procedure sp_rename:

EXEC sp_rename 
    @objname = 'sales.product_catalog',
    @newname = 'product_list';

Trong câu lệnh này:

• Đầu tiên, truyền tên của view mà bạn muốn đổi tên vào tham số @objname và tên view mới vào tham số @newname. Lưu ý rằng trong tham số @objectname bạn phải chỉ định tên lược đồ của view, còn trong tham số @newname thì không cần.
• Thứ hai, thực thi câu lệnh.

Stored procedure sp_rename sẽ trả về thông báo sau:

Caution: Changing any part of an object name could break scripts and stored procedures.

Liệt kê danh sách view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách liệt kê tất cả các view trong cơ sở dữ liệu SQL Server bằng cách truy vấn danh mục hệ thống.

Để liệt kê tất cả các view trong cơ sở dữ liệu SQL Server, bạn truy vấn danh mục hệ thống sys.views hoặc sys.objects. Đây là một ví dụ:

SELECT 
    OBJECT_SCHEMA_NAME(v.object_id) schema_name,
    v.name
FROM 
    sys.views as v;

Truy vấn trả về danh sách tên lược đồ và tên view sau đây:

Trong ví dụ này, chúng tôi đã sử dụng hàm OBJECT_SCHEMA_NAME() để lấy tên lược đồ của view.

Truy vấn sau đây trả về danh sách các view thông qua sys.objects:

SELECT 
    OBJECT_SCHEMA_NAME(o.object_id) schema_name,
    o.name
FROM
    sys.objects as o
WHERE
    o.type = 'V';

Tạo stored procedure liệt kê danh sách view trong cơ sở dữ liệu SQL Server

Stored procedure sau đây sử dụng truy vấn ở trên để liệt kê tất cả view trong cơ sở dữ liệu SQL Server dựa trên tên lược đồ đầu vào và tên view:

CREATE PROC usp_list_views(
    @schema_name AS VARCHAR(MAX)  = NULL,
    @view_name AS VARCHAR(MAX) = NULL
)
AS
SELECT 
    OBJECT_SCHEMA_NAME(v.object_id) schema_name,
    v.name view_name
FROM 
    sys.views as v
WHERE 
    (@schema_name IS NULL OR 
    OBJECT_SCHEMA_NAME(v.object_id) LIKE '%' + @schema_name + '%') AND
    (@view_name IS NULL OR
    v.name LIKE '%' + @view_name + '%');

Ví dụ: nếu bạn muốn biết các view có chứa từ sales, bạn có thể gọi stored procedure usp_list_view như sau:

EXEC usp_list_views @view_name = 'sales'

Đây là kết quả:

Lấy thông tin view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu những cách khác nhau để lấy thông tin của view trong cơ sở dữ liệu SQL Server.

Lấy thông tin view bằng sys.sql_modules trong SQL Server

Để lấy thông tin của view, bạn sử dụng danh mục hệ thống sys.sql_modules và hàm OBJECT_ID() như sau:

SELECT
    definition,
    uses_ansi_nulls,
    uses_quoted_identifier,
    is_schema_bound
FROM
    sys.sql_modules
WHERE
    object_id = object_id('sales.daily_sales');

Trong truy vấn này, bạn truyền tên của view cho hàm OBJECT_ID() trong mệnh đề WHERE. Hàm OBJECT_ID() trả về mã định danh của đối tượng cơ sở dữ liệu schema-scoped.

Đây là đầu ra:

Lưu ý: bạn cần xuất kết quả sang định dạng văn bản để xem câu lệnh SELECT rõ ràng như hình trên.

Để hiển thị kết quả dưới dạng văn bản, từ trình soạn thảo truy vấn, bạn nhấn phím tắt Ctrl-T hoặc nhấp vào nút Results to Text như trong ảnh chụp màn hình sau:

Lấy thông tin view bằng stored procedure sp_helptext trong SQL Server

Stored procedure sp_helptext trả về định nghĩa của một đối tượng người dùng định nghĩa.

Để lấy thông tin của view, bạn truyền tên view cho stored procedure sp_helptext. Ví dụ: câu lệnh sau trả về thông tin của view sales.product_catalog:

EXEC sp_helptext 'sales.product_catalog';

Hình ảnh sau đây cho thấy đầu ra:

Lấy thông tin view bằng hàm OBJECT_DEFINITION()

Một cách khác để lấy thông tin view là sử dụng hàm OBJECT_DEFINITION() và OBJECT_ID() như sau:

SELECT 
    OBJECT_DEFINITION(
        OBJECT_ID(
            'sales.staff_sales'
        )
    ) view_info;

Hình ảnh sau đây cho thấy đầu ra:

Tạo view được lập chỉ mục trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách tạo view được lập chỉ mục (indexed view) trong SQL Server để lưu trữ dữ liệu vật lý trong cơ sở dữ liệu.

Giới thiệu về view được lập chỉ mục của SQL Server

View trong SQL Server thông thường là các truy vấn được lưu trong cơ sở dữ liệu. Nó cung cấp một số lợi ích như tính đơn giản của truy vấn, tính nhất quán logic nghiệp vụ và bảo mật. Tuy nhiên, chúng không giúp cải thiện hiệu năng của truy vấn.

Không giống như những view thông thường, view được lập chỉ mục (indexed view) là những view có thể lưu trữ dữ liệu vật lý như bảng do đó có thể cung cấp một số lợi ích về hiệu suất nếu chúng được sử dụng một cách thích hợp.

Để tạo view được lập chỉ mục, bạn sử dụng các bước sau:

• Đầu tiên, tạo một view sử dụng tùy chọn WITH SCHEMABINDING để liên kết view với lược đồ của các bảng.
• Thứ hai, tạo một unique clustered index cho view.

Vì tùy chọn WITH SCHEMABINDING, bạn buộc phải xóa view được lập chỉ mục trước khi thay đổi cấu trúc của các bảng ảnh hưởng đến định nghĩa của view được lập chỉ mục.

Ngoài ra, SQL Server yêu cầu tất cả các đối tượng được tham chiếu trong view được lập chỉ mục phải được đặt tên theo quy ước bao gồm hai phần là lược đồ và tên, ví dụ: schema.object và tất cả các đối tượng được tham chiếu đều nằm trong cùng một cơ sở dữ liệu.

Khi dữ liệu của các bảng thay đổi, dữ liệu trong view được lập chỉ mục cũng được tự động cập nhật. Điều này gây ra một chi phí ghi cho các bảng được tham chiếu.

Điều này có nghĩa là khi bạn ghi vào bảng, SQL Server cũng phải ghi vào chỉ mục của view. Do đó, bạn chỉ nên tạo view được lập chỉ mục cho các bảng không cập nhật dữ liệu thường xuyên.

Tạo view được lập chỉ mục trong SQL Server

Câu lệnh dưới tạo một view được lập chỉ mục dựa trên các cột của bảng production.products, production.brands và production.categories từ các cơ sở dữ liệu mẫu:

CREATE VIEW product_master
WITH SCHEMABINDING
AS 
SELECT
    product_id,
    product_name,
    model_year,
    list_price,
    brand_name,
    category_name
FROM
    production.products p
INNER JOIN production.brands b 
    ON b.brand_id = p.brand_id
INNER JOIN production.categories c 
    ON c.category_id = p.category_id;

Lưu ý: thêm tùy chọn WITH SCHEMABINDING sau tên view. Phần còn lại giống như một view thông thường.

Trước khi tạo một unique clustered index cho view, hãy kiểm tra thống kê chi phí I/O của truy vấn bằng cách thực hiện truy vấn dữ liệu của view kèm theo lệnh SET STATISTICS IO:

SET STATISTICS IO ON
GO

SELECT 
    * 
FROM
    production.product_master
ORDER BY
    product_name;
GO

SQL Server trả về thống kê chi phí I/O của truy vấn như sau:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'products'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'categories'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'brands'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Sau đó tạo một unique clustered index cho view như sau:

CREATE UNIQUE CLUSTERED INDEX 
    ucidx_product_id 
ON production.product_master(product_id);

Câu lệnh này làm cho view có dữ liệu vật lý trong cơ sở dữ liệu.

Bạn cũng có thể thêm một non-clustered index trên cột product_name của view như sau:

CREATE NONCLUSTERED INDEX 
    ucidx_product_name
ON production.product_master(product_name);

Bây giờ, nếu bạn truy vấn dữ liệu trong view, bạn sẽ nhận thấy rằng số liệu thống kê I/O đã thay đổi như sau:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'product_master'. Scan count 1, logical reads 6, physical reads 1, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Thay vì đọc dữ liệu từ ba bảng, SQL Server hiện đọc dữ liệu trực tiếp từ view product_master.

Lưu ý: tính năng này chỉ có trên SQL Server Enterprise Edition. Nếu bạn sử dụng SQL Server Standard hoặc Developer Edition, bạn phải sử dụng WITH (NOEXPAND) trong mệnh đề FROM của truy vấn mà bạn muốn sử dụng view như truy vấn sau:

SELECT 
    * 
FROM
    production.product_master WITH (NOEXPAND)
ORDER BY
    product_name;

Trong hướng dẫn này, bạn đã tìm hiểu cách quản lý view như tạo view mới, định nghĩa lại view, đổi tên view, xóa view, lấy thông tin view, tạo view được lập chỉ mục của SQL Server.

SQL Server • SQL Server nâng cao