Hàm Kiểu Bảng trong Sql Server

Hàm kiểu bảng (table-valued function) là một loại hàm do người dùng định nghĩa, trong đó kết quả trả về là một cấu trúc kiểu bảng và bạn có thể sử dụng như một bảng thông thường, như SELECT hay JOIN với nó. Trong Management Studio, hàm kiểu bảng xuất hiện ở mục “Table-Valued Functions” ở bên trong “Functions”:

Hàm kiểu bảng được chia làm hai loại: in-line và multi-statement. Ta sẽ tìm hiểu cách viết hai loại này và sự khác biệt của chúng trong bài này.
Ở mức đơn giản nhất hàm kiểu bảng có thể chỉ là một câu lệnh SELECT đơn thuần. Ví dụ tôi cần viết một hàm lấy về thông tin của các sản phẩm dựa trên ModelID:

--ví dụ 1
USE AdventureWorks
GO
CREATE FUNCTION dbo.fn_ProductInfoByModelID(@p INT) RETURNS TABLE
AS
RETURN
 SELECT P.ProductID, P.Name, P.ProductNumber
 FROM Production.Product P 
 WHERE P.ProductModelID = @p
GO
-- gọi hàm trực tiếp
SELECT * FROM dbo.fn_ProductInfoByModelID(5)
 
--hoặc join với hàm
SELECT TH.*, P.Name
FROM Production.TransactionHistory TH
JOIN dbo.fn_ProductInfoByModelID(5) P ON TH.ProductID = P.ProductID

Hàm ở ví dụ trên là thuộc loại in-line, trong đó toàn bộ phần thân của hàm là một câu lệnh duy nhất. Có thể là một lệnh SELECT; hoặc nhiều lệnh SELECT được nối với nhau bằng UNION. Tất nhiên bạn vẫn có thể dùng sub-query hoặc common table expression khi cần thiết. Miễn là khi bạn copy toàn bộ phần thân của hàm và thực hiện đoạn code đó, chỉ có duy nhất một câu lệnh được gửi tới server.
Với những tình huống phức tạp hơn, một câu lệnh duy nhất có thể không đủ để giải quyết được vấn đề. Lúc đó bạn cần viết hàm kiểu multi-statement. Ví dụ tôi cần một hàm chuyển đổi chuỗi phân cách bằng dấu phẩy (,) thành bảng:

--ví dụ 2
CREATE FUNCTION dbo.fnCSVStr2Table(@CSVStr VARCHAR(8000)) 
RETURNS @Tbl TABLE (ValueColumn VARCHAR(1000))
AS
BEGIN
 DECLARE @SubStr VARCHAR(100), @i INT
 
 SET @i = CHARINDEX(',', @CSVStr, 0)
 WHILE @i > 0
 BEGIN
  SET @SubStr = LEFT(@CSVStr,@i-1)
  INSERT INTO @Tbl
  SELECT @SubStr
 
  SET @CSVStr = SUBSTRING(@CSVStr, @i+1,8000)
  SET @i = CHARINDEX(',', @CSVStr, 0)
 END 
 
 INSERT INTO @Tbl
 SELECT LTRIM(RTRIM(@CSVStr))
 
 RETURN
END

Ở ví dụ trên bạn thấy là việc khai báo hàm multi-statement khác với hàm in-line. Bạn phải khai báo cấu trúc của bảng kết quả, và phần thân của hàm là các lệnh thao tác dữ liệu trên bảng kết quả và kết thúc là câu lệnh RETURN. Câu lệnh RETURN này không kèm theo tham số nào, vì kết quả trả về đã nằm trong bảng @Tbl. Điều này ngược lại với hàm in-line, trong đó lệnh RETURN được theo sau là lệnh SELECT chính là nội dung của hàm.

Khác biệt giữa hàm in-line và hàm multi-statement

Như đã thấy ở trên, hàm in-line chỉ yêu cầu khai báo đơn giản, bạn có thể thay đổi cấu trúc của tập kết quả tùy ý (ví dụ thêm một cột vào tập kết quả). Ngược lại, với hàm kiểu multi-statement bạn cần khai báo trước cấu trúc của tập kết quả, và nếu bạn thay đổi cấu trúc này thì cũng cần phải sửa lại khai báo.
Tuy nhiên khác biệt quan trọng nhất giữa hai loại hàm trên là ở hiệu năng. Khi bạn JOIN với một hàm in-line, bộ Optimizer có thể truy nhập vào thông tin index và statistics của các bảng được sử dụng trong hàm khi lựa chọn phương án thực thi. Nó biết các bảng nào được sử dụng và vì chỉ có một lệnh được dùng trong hàm, nên có thể viết lại câu lệnh và gộp chung phương án thực thi của hàm vào phương án chung của cả câu lệnh. Điều này tương tự như khi bạn JOIN với một view, bộ Optimizer sẽ mở rộng câu lệnh dùng trong view, gộp chung với câu lệnh chính và tối ưu hóa cho cả câu lệnh to. Vì vậy kết quả sẽ là câu lệnh luôn có một phương án thực thi tối ưu.
Ngược lại, với hàm multi-statement, bộ Optimizer “mù tịt” với những gì xảy ra bên trong hàm. Nó vẫn thực hiện tối ưu hóa cho từng lệnh bên trong hàm, nhưng không thể “xé nhỏ” nó ra để tối ưu chung cho cả câu lệnh. Nói cách khác, phương án thực thi của hàm nằm độc lập với phương án thực thi của câu lệnh chính. Trên thực tế, bộ Optimizer luôn giả định tập kết quả do hàm trả về chứa 1 bản ghi khi thực hiện tối ưu câu lệnh. Giả định này là không đúng trong đại đa số trường hợp nên thường dẫn đến phương án thực thi dở tệ.

Ví dụ

Hãy quan sát phương án thực thi của lệnh JOIN với hàm fn_ProductInfoByModelID kiểu in-line trong ví dụ ở đầu bài:

--ví dụ 3
SELECT TH.*, P.Name
FROM Production.TransactionHistory TH
JOIN dbo.fn_ProductInfoByModelID(5) P ON TH.ProductID = P.ProductID

Bạn có thể thấy không có bước gọi đến hàm mà là hai bước Clustered Index Scan trên hai bảng và sau đó là Hash Match, giống như câu lệnh JOIN trực tiếp giữa hai bảng. Phương án thực thi này giống hệt với khi bạn thực hiện câu lệnh:

--ví dụ 4
SELECT *
FROM Production.TransactionHistory TH
JOIN Production.Product P ON TH.ProductID = P.ProductID
WHERE P.ProductModelID = 5

Sở dĩ như vậy là vì bộ Optimizer đã sục vào bên trong hàm và hòa tan nó ra để tạo một phương án thực thi tối ưu chung cho cả câu lệnh. Nay ta hãy tạo một phiên bản của hàm fn_ProductInfoByModelID trên ở dạng multi-statement:

--ví dụ 5
CREATE FUNCTION dbo.fn_ProductInfoByModelID_MSTV(@p INT) 
RETURNS @t TABLE(ProductID INT, Name NVARCHAR(50), ProductNumber NVARCHAR(25))
AS
BEGIN
 INSERT INTO @t
 SELECT P.ProductID, P.Name, P.ProductNumber
 FROM Production.Product P 
 WHERE P.ProductModelID = @p
 
 RETURN
END

Và JOIN với nó giống như đã làm ở trên và xem phương án thực thi của nó như thế nào:

--ví dụ 6
SELECT *
FROM Production.TransactionHistory TH
JOIN dbo.fn_ProductInfoByModelID_MSTV(5) P ON TH.ProductID = P.ProductID

Bây giờ bước thực hiện hàm đã trở thành một bước độc lập (trên cùng bên trái), và chi phí của nó được gán là 0% vì bộ Optimizer không có tí thông tin nào về bên trong của hàm. Khỏi cần nói phương án thực thi giờ đã trở nên rườm rà như thế nào. Và đây là thống kê vào/ra của hai lệnh gọi hàm:
Lệnh gọi hàm in-line (ví dụ 5):

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 ‘TransactionHistory’. Scan count 1, logical reads 792, physical reads 8, read-ahead reads 788.
Table ‘Product’. Scan count 1, logical reads 15, physical reads 3, read-ahead reads 14

Lệnh gọi hàm multi-statement (ví dụ 6):

Table ‘TransactionHistory’. Scan count 10, logical reads 6263, physical reads 7, read-ahead reads 768.
Table ‘#0BC6C43E’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

Khác biệt như vậy là khá đáng kể, số lần đọc (logical read) trên bảng TransactionHistory tăng từ 792 lên 6263, và số lần thực hiện (scan count) tăng từ 1 lên 10. Bạn có thể hỏi 10 scan count là từ đâu ra? Đó chính là số bản ghi mà hàm trả về.

Kết luận

Bạn dùng hàm kiểu bảng khi dữ liệu trả về cần ở dạng bảng. Nó mềm dẻo hơn view vì có thể tiếp nhận tham số đầu vào và có thể chứa nhiều lệnh thao tác dữ liệu bên trong hàm (với kiểu multi-statement).
Trong hai kiểu hàm in-line và multi-statement, trước hết bạn nên chọn viết kiểu in-line vì lý do khai báo đơn giản và có ưu thế về hiệu năng. Khi cần thiết phải dùng hàm multi-statement bạn nên khống chế số bản ghi trả về ở số lượng nhỏ.

Phiên bản áp dụng: SQL Server 2005 trở lên

Nguồn: Sqlviet.com

Unknown

Add Comment

bài tập SQL, SQL Server, Truy vấn SQL

Thứ Sáu, 6 tháng 4, 2012

Tweet

Kênh tư vấn
Published: 2012-04-06T19:07:00-07:00
Title:Hàm Kiểu Bảng trong Sql Server
Rating: 5 On 22 reviews

Related Articles