Thứ Tư, Tháng Bảy 6, 2022
HomeWikiView trong SQL Server | Comdy

View trong SQL Server | Comdy

View trong SQL Server

  • Trung Nguyen
  • 13/05/2020

  • 16 min read

Trong hướng dẫn này, bạn sẽ khám phá về cách quản trị view như tạo view mới, xóa view và update tài liệu của những bảng bên dưới trải 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ả.

Bạn đang đọc: View trong SQL Server | Comdy

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ựa như, bạn hoàn toàn có thể lưu truy vấn này vào một file, mở nó và thực thi lại .SQL Server cung ứng một cách tốt hơn để lưu truy vấn này trong cơ sở tài liệu trải qua view .View là một truy vấn được đặt tên và được tàng trữ trong cơ sở tài liệu được 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ẽ triển khai 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 tàng trữ tài liệu ngoại trừ những view được lập chỉ mục .View hoàn toàn có thể chứa những cột từ nhiều bảng bằng cách sử dụng những mệnh đề join hoặc chỉ là một tập hợp con những cột của một bảng. Điều này làm cho view có ích trong việc trừu tượng hóa hoặc ẩn những truy vấn phức tạp .Hình ảnh sau đây minh họa view gồm có những cột từ nhiều bảng :View trong SQL Server

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

Nói chung, view phân phối những ưu điểm sau :

Bảo mật

Bạn hoàn toàn có thể hạn chế người dùng truy vấn trực tiếp vào bảng và được cho phép họ truy vấn tập hợp con tài liệu trải qua view .Ví dụ : bạn hoàn toàn có thể được cho phép người dùng truy vấn tên người mua, điện thoại cảm ứng, email qua view nhưng hạn chế họ truy vấn vào thông tin tài khoản ngân hàng nhà nước và những thông tin nhạy cảm khác .

Sự đơn giản

Một cơ sở tài liệu quan hệ hoàn toàn có thể có nhiều bảng với những 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 vất vả cho việc điều hướng .Tuy nhiên, bạn hoàn toàn có thể đơn giản hóa những truy vấn phức tạp với những mệnh đề join và điều kiện kèm theo 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ó đồng điệu, bạn hoàn toàn có thể ẩn logic và những phép tính phức tạp vào trong view .Khi view được định nghĩa, bạn hoàn toàn có thể tham chiếu logic từ view thay vì viết lại nó trong những truy vấn riêng không liên quan gì đến nhau .

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_itemsproducts từ các cơ sở dữ liệu mẫu để minh họa tạo view trong SQL Server.

Ví dụ CREATE 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_itemsproducts:

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 :Ví dụ CREATE VIEW trong SQL Server

Đị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 :Định nghĩa lại view trong SQL Server

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 :Tạo view sử dụng hàm tập hợp trong SQL Server

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, những 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_salessales.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_salessales.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 chú ý quan tâm rằng toàn bộ những đối tượng người tiêu dùng nhờ vào vào view hoàn toàn có thể gặp lỗi. Chúng gồm có stored procedure, hàm người dùng tự định nghĩa, trigger, truy vấn, những view khác và những ứng dụng khách .Do đó, sau khi đổi tên view, bạn phải bảo vệ rằng tổng thể những đối tượng người dù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 những bước sau :Đầu tiên, trong Object Explorer, lan rộng ra Database, chọn tên cơ sở tài liệu chứa view mà bạn muốn đổi tên và lan rộng ra 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.

Đổi tên view bằng Server Server Management Studio (SSMS)Thứ ba, nhập tên mới cho view .Đổi tên view bằng Server Server Management Studio (SSMS)

Đổ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ẽ khám phá cách liệt kê tổng thể những view trong cơ sở tài liệu SQL Server bằng cách truy vấn hạng mục mạng lưới 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ề list tên lược đồ và tên view sau đây :Liệt kê danh sách view trong SQL Server

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ê toàn bộ view trong cơ sở tài liệu SQL Server dựa trên tên lược đồ nguồn 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à hiệu quả :Tạo stored procedure liệt kê danh sách view trong cơ sở dữ liệu SQL Server

Lấy thông tin view trong SQL Server

Trong phần này, bạn sẽ tìm hiểu và khám phá những cách khác nhau để lấy thông tin của view trong cơ sở tài 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ấy thông tin view bằng sys.sql_modules trong SQL Server

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 sys.sql_modules trong SQL Server

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 stored procedure sp_helptext trong SQL Server

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()OBJECT_ID() như sau:

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

Hình ảnh sau đây cho thấy đầu ra :Lấy thông tin view bằng hàm OBJECT_DEFINITION()

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

Trong phần này, bạn sẽ tìm hiểu và khám phá cách tạo view được lập chỉ mục ( indexed view ) trong SQL Server để tàng trữ tài liệu vật lý trong cơ sở tài 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ì là những truy vấn được lưu trong cơ sở tài liệu. Nó phân phối 1 số ít quyền lợi như tính đơn thuần của truy vấn, tính đồng điệu logic nhiệm vụ và bảo mật thông tin. Tuy nhiên, chúng không giúp cải tổ hiệu năng của truy vấn .Không giống như những view thường thì, view được lập chỉ mục ( indexed view ) là những view hoàn toàn có thể tàng trữ tài liệu vật lý như bảng do đó hoàn toàn có thể phân phối 1 số ít quyền lợi 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 những 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 tài liệu của những bảng biến hóa, tài liệu trong view được lập chỉ mục cũng được tự động hóa update. Điều này gây ra một ngân sách ghi cho những 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 những bảng không update tài liệu tiếp tục .

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.brandsproduction.categories từ các cơ sở dữ liệu mẫu:

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

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ê ngân sách 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ó tài liệu vật lý trong cơ sở tài 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 tài liệu trong view, bạn sẽ nhận thấy rằng số liệu thống kê I / O đã đổi khác 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 đã khám phá cách quản trị 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 .

Nếu Comdy hữu ích và giúp bạn tiết kiệm thời gian

Bạn hoàn toàn có thể sung sướng tắt trình chặn quảng cáo ❤ ️ để tương hỗ chúng tôi duy trì hoạt động giải trí của website .

Source: https://entechgadget.com
Category: Wiki

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Bài viết hay nhất

DANH MỤC WEBSITE