User-Defined Functions (UDFs)

User-Defined Functions (UDFs) trong SQL Server cho phép đóng gói logic tái sử dụng, trả về giá trị đơn hoặc tập kết quả dạng bảng. Khác với Stored Procedures, functions có thể được dùng trực tiếp trong câu SELECT, WHERE, và JOIN.

1. Loại Functions

Tổng quan

User-Defined Functions
├── Scalar Functions          → Trả về một giá trị đơn
├── Table-Valued Functions
│   ├── Inline TVF (iTVF)    → Một câu SELECT duy nhất
│   └── Multi-Statement TVF  → Nhiều câu lệnh, explicit table variable
└── CLR Aggregate Functions  → Tùy chỉnh aggregate (cần CLR)

Scalar Functions

-- Syntax cơ bản
CREATE FUNCTION dbo.CalculateAge
(
    @BirthDate DATE
)
RETURNS INT -- Trả về một giá trị đơn
AS
BEGIN
    DECLARE @Age INT;
    
    SET @Age = DATEDIFF(YEAR, @BirthDate, GETDATE())
        - CASE 
            WHEN MONTH(@BirthDate) > MONTH(GETDATE())
              OR (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()))
            THEN 1 
            ELSE 0 
          END;
    
    RETURN @Age;
END;
GO

-- Sử dụng Scalar Function trong SELECT
SELECT 
    EmployeeId,
    FullName,
    BirthDate,
    dbo.CalculateAge(BirthDate) AS Age
FROM Employees;

-- Trong WHERE clause
SELECT * FROM Employees
WHERE dbo.CalculateAge(BirthDate) >= 30;

Inline Table-Valued Functions (iTVF)

-- Inline TVF: Chỉ một câu SELECT, không có BEGIN...END
CREATE FUNCTION dbo.GetOrdersByCustomer
(
    @CustomerId INT,
    @MinAmount  DECIMAL(18,2) = 0
)
RETURNS TABLE -- Không chỉ định cấu trúc table
AS
RETURN
(
    SELECT 
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        c.CustomerName
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    WHERE 
        o.CustomerId = @CustomerId
        AND o.TotalAmount >= @MinAmount
);
GO

-- Sử dụng iTVF: dùng như view có tham số
SELECT * FROM dbo.GetOrdersByCustomer(5, 100.00);

-- JOIN với iTVF (CROSS APPLY)
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetOrdersByCustomer(c.CustomerId, 0) o
WHERE c.IsActive = 1;

-- OUTER APPLY: giữ lại customers không có orders
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.TotalAmount
FROM Customers c
OUTER APPLY dbo.GetOrdersByCustomer(c.CustomerId, 0) o;

Multi-Statement Table-Valued Functions (msTVF)

-- msTVF: Có BEGIN...END, định nghĩa rõ ràng cấu trúc return table
CREATE FUNCTION dbo.GetEmployeeHierarchy
(
    @ManagerId INT
)
RETURNS @Result TABLE -- Khai báo return table
(
    EmployeeId  INT,
    FullName    NVARCHAR(200),
    Level       INT,
    ManagerId   INT
)
AS
BEGIN
    -- Có thể có nhiều câu lệnh
    INSERT INTO @Result (EmployeeId, FullName, Level, ManagerId)
    VALUES (@ManagerId, (SELECT FullName FROM Employees WHERE EmployeeId = @ManagerId), 0, NULL);

    DECLARE @CurrentLevel INT = 0;

    WHILE EXISTS (
        SELECT 1 FROM Employees e
        INNER JOIN @Result r ON e.ManagerId = r.EmployeeId
        WHERE r.Level = @CurrentLevel
        AND e.EmployeeId NOT IN (SELECT EmployeeId FROM @Result)
    )
    BEGIN
        INSERT INTO @Result (EmployeeId, FullName, Level, ManagerId)
        SELECT 
            e.EmployeeId,
            e.FullName,
            @CurrentLevel + 1,
            e.ManagerId
        FROM Employees e
        INNER JOIN @Result r ON e.ManagerId = r.EmployeeId
        WHERE r.Level = @CurrentLevel
        AND e.EmployeeId NOT IN (SELECT EmployeeId FROM @Result);

        SET @CurrentLevel = @CurrentLevel + 1;
    END;

    RETURN; -- Không cần return value, data đã trong @Result
END;
GO

-- Sử dụng msTVF
SELECT EmployeeId, FullName, Level
FROM dbo.GetEmployeeHierarchy(1)
ORDER BY Level, FullName;

2. So Sánh iTVF vs msTVF: Performance

-- DEMO: So sánh execution plan

-- iTVF: Optimizer có thể thấy bên trong và optimize
CREATE FUNCTION dbo.GetActiveOrders_iTVF(@Status NVARCHAR(50))
RETURNS TABLE AS RETURN
(
    SELECT OrderId, CustomerId, TotalAmount
    FROM Orders
    WHERE Status = @Status
);

-- msTVF: Optimizer không nhìn thấy bên trong
CREATE FUNCTION dbo.GetActiveOrders_msTVF(@Status NVARCHAR(50))
RETURNS @T TABLE (OrderId INT, CustomerId INT, TotalAmount DECIMAL(18,2))
AS
BEGIN
    INSERT @T
    SELECT OrderId, CustomerId, TotalAmount
    FROM Orders
    WHERE Status = @Status;
    RETURN;
END;

-- Query với JOIN - iTVF sẽ tốt hơn vì optimizer có thể push predicate vào function
SELECT c.CustomerName, o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetActiveOrders_iTVF('Shipped') o  -- Better
WHERE c.CustomerId = o.CustomerId
  AND c.Region = 'North';

Kết luận: Luôn ưu tiên iTVF khi có thể.

3. Deterministic vs Non-Deterministic Functions

-- DETERMINISTIC: Cùng input → luôn cùng output
-- Ví dụ: UPPER(), LOWER(), LEN(), DATEADD(), SQRT()

-- NON-DETERMINISTIC: Cùng input có thể khác output
-- Ví dụ: GETDATE(), RAND(), NEWID(), GETUTCDATE()

-- Tại sao quan trọng?
-- 1. Indexed columns/computed columns chỉ chấp nhận deterministic functions
-- 2. Indexed views yêu cầu deterministic
-- 3. WITH SCHEMABINDING function được đánh dấu deterministic nếu eligible

-- UDF deterministic khi:
-- - Sử dụng WITH SCHEMABINDING
-- - Chỉ gọi deterministic built-in functions
-- - Không access database objects (bảng, views...)

CREATE FUNCTION dbo.FormatCurrency
(
    @Amount DECIMAL(18,2),
    @CurrencyCode CHAR(3)
)
RETURNS NVARCHAR(50)
WITH SCHEMABINDING -- Cần để mark deterministic
AS
BEGIN
    RETURN @CurrencyCode + ' ' + FORMAT(@Amount, 'N2');
END;
GO

-- Kiểm tra function có deterministic không
SELECT 
    name, 
    is_deterministic 
FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type IN ('FN', 'TF', 'IF');

-- Dùng deterministic function trong computed column
ALTER TABLE Products
ADD FormattedPrice AS dbo.FormatCurrency(Price, 'USD') PERSISTED;
-- PERSISTED: lưu vật lý, cập nhật khi Price thay đổi

4. Function Restrictions (Những gì KHÔNG được làm)

-- Functions KHÔNG được phép:
-- 1. INSERT/UPDATE/DELETE trên real tables
-- 2. CREATE/DROP tables (ngoại trừ table variables)
-- 3. Gọi Stored Procedures
-- 4. TRY...CATCH (có thể dùng trong msTVF nhưng hạn chế)
-- 5. Dynamic SQL với EXEC() (sp_executesql với SELECT được phép trong một số trường hợp)
-- 6. Thay đổi state database (SET options, COMMIT/ROLLBACK cấp cao)

-- VI DỤ LỖI:
CREATE FUNCTION dbo.BadFunction(@Id INT)
RETURNS INT
AS
BEGIN
    -- LỖI: Không thể INSERT vào real table trong function
    INSERT INTO AuditLog (Action) VALUES ('Called'); -- ERROR!
    
    RETURN 1;
END;

-- GIẢI PHÁP: Dùng Stored Procedure thay vì Function khi cần side effects

5. Performance Caveat của Scalar UDFs

Vấn đề Row-by-Row Execution

-- Scalar UDF chạy row-by-row, KHÔNG thể parallelize (trước SQL 2019)
CREATE FUNCTION dbo.GetCustomerTier(@CustomerId INT)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @TotalSpent DECIMAL(18,2);
    
    SELECT @TotalSpent = SUM(TotalAmount)
    FROM Orders
    WHERE CustomerId = @CustomerId;
    
    RETURN CASE 
        WHEN @TotalSpent >= 10000 THEN 'Platinum'
        WHEN @TotalSpent >= 5000  THEN 'Gold'
        WHEN @TotalSpent >= 1000  THEN 'Silver'
        ELSE 'Bronze'
    END;
END;
GO

-- Query này sẽ gọi function 1 TRIỆU lần nếu có 1 triệu customers!
-- Serial execution, rất chậm
SELECT 
    CustomerId,
    CustomerName,
    dbo.GetCustomerTier(CustomerId) AS Tier -- Row-by-row!
FROM Customers;

-- GIẢI PHÁP: Viết set-based thay thế
SELECT
    CustomerId,
    CustomerName,
    CASE 
        WHEN SUM(o.TotalAmount) >= 10000 THEN 'Platinum'
        WHEN SUM(o.TotalAmount) >= 5000  THEN 'Gold'
        WHEN SUM(o.TotalAmount) >= 1000  THEN 'Silver'
        ELSE 'Bronze'
    END AS Tier
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.CustomerName;

6. Scalar UDF Inlining trong SQL Server 2019+

-- SQL Server 2019 tự động inline eligible scalar UDFs
-- Biến scalar UDF thành relational expression, cho phép:
-- - Parallelism
-- - Cost-based optimization  
-- - Predicate pushdown

-- Điều kiện để được inline tự động:
-- ✅ Chỉ một RETURN statement
-- ✅ Không gọi EXECUTE
-- ✅ Không đệ quy
-- ✅ Không dùng TRY...CATCH
-- ✅ Không dùng table variables (ngoại trừ trong subquery)
-- ✅ Không có side effects
-- ❌ Không gọi RAND(), NEWID() (non-deterministic)

-- Kiểm tra function có được inline không
SELECT 
    o.name AS FunctionName,
    m.is_inlineable
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.type = 'FN'; -- Scalar functions

-- Force inline cho một query cụ thể
SELECT 
    CustomerId,
    dbo.GetCustomerTier(CustomerId) AS Tier
FROM Customers
OPTION (USE HINT('ENABLE_TSQL_SCALAR_UDF_INLINING')); -- Force enable

-- Disable inline nếu gây vấn đề
SELECT 
    CustomerId,
    dbo.GetCustomerTier(CustomerId) WITH (INLINE = OFF) AS Tier -- SQL 2019+
FROM Customers;

-- Database level control
ALTER DATABASE MyDatabase
SET TSQL_SCALAR_UDF_INLINING = ON; -- Default ON trong compatibility 150+

7. Rewriting Scalar UDFs như iTVFs (Performance Pattern)

Đây là pattern quan trọng nhất để cải thiện hiệu năng function.

-- BEFORE: Scalar UDF - chậm, không parallel
CREATE FUNCTION dbo.GetProductDiscount_Scalar(@ProductId INT, @Quantity INT)
RETURNS DECIMAL(5,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(5,2) = 0;
    DECLARE @Category NVARCHAR(50);
    
    SELECT @Category = CategoryName
    FROM Products p
    JOIN Categories c ON p.CategoryId = c.CategoryId
    WHERE p.ProductId = @ProductId;
    
    IF @Category = 'Electronics' AND @Quantity >= 10
        SET @Discount = 0.15;
    ELSE IF @Quantity >= 20
        SET @Discount = 0.10;
    ELSE IF @Quantity >= 5
        SET @Discount = 0.05;
    
    RETURN @Discount;
END;
GO

-- AFTER: Inline TVF - nhanh, optimizer can see inside
CREATE FUNCTION dbo.GetProductDiscount_iTVF(@ProductId INT, @Quantity INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CASE 
            WHEN c.CategoryName = 'Electronics' AND @Quantity >= 10 THEN 0.15
            WHEN @Quantity >= 20 THEN 0.10
            WHEN @Quantity >= 5  THEN 0.05
            ELSE 0.00
        END AS Discount
    FROM Products p
    JOIN Categories c ON p.CategoryId = c.CategoryId
    WHERE p.ProductId = @ProductId
);
GO

-- Sử dụng iTVF với CROSS APPLY
SELECT 
    od.OrderDetailId,
    od.ProductId,
    od.Quantity,
    d.Discount,
    od.UnitPrice * od.Quantity * (1 - d.Discount) AS FinalAmount
FROM OrderDetails od
CROSS APPLY dbo.GetProductDiscount_iTVF(od.ProductId, od.Quantity) d;

8. CLR Aggregate Functions

-- CLR Aggregate cần code C# và assembly registration
-- Ví dụ use case: Concatenate strings (như STRING_AGG trong SQL 2017+)

-- Sau khi deploy CLR assembly:
-- CREATE AGGREGATE dbo.StringConcat(@value NVARCHAR(MAX), @delimiter NVARCHAR(10))
-- RETURNS NVARCHAR(MAX)
-- EXTERNAL NAME MyAssembly.[MyNamespace.StringConcatAggregate];

-- Trong SQL Server 2017+, dùng STRING_AGG thay vì CLR:
SELECT 
    DepartmentId,
    STRING_AGG(FullName, ', ') WITHIN GROUP (ORDER BY FullName) AS EmployeeNames
FROM Employees
GROUP BY DepartmentId;

9. System Functions Overview

Metadata Functions

-- OBJECT_ID: Lấy object_id từ tên
SELECT OBJECT_ID('dbo.Orders');          -- Trả về INT hoặc NULL
SELECT OBJECT_ID('dbo.Orders', 'U');     -- 'U' = User table

-- OBJECT_NAME: Ngược lại OBJECT_ID
SELECT OBJECT_NAME(OBJECT_ID('dbo.Orders'));

-- SCHEMA_NAME: Lấy tên schema từ schema_id
SELECT SCHEMA_NAME(1); -- 'dbo'

-- Kiểm tra tồn tại trước khi create/drop
IF OBJECT_ID('dbo.MyFunction', 'FN') IS NOT NULL
    DROP FUNCTION dbo.MyFunction;
GO
CREATE FUNCTION dbo.MyFunction...

-- SQL 2016+: DROP IF EXISTS
DROP FUNCTION IF EXISTS dbo.MyFunction;

-- COLUMNPROPERTY: Thông tin về column
SELECT COLUMNPROPERTY(OBJECT_ID('Orders'), 'TotalAmount', 'Precision');

-- INDEX_COL: Tên column trong index
SELECT INDEX_COL('dbo.Orders', 1, 1); -- (table, index_id, key_ordinal)

Security Functions

-- USER_NAME(): Tên user hiện tại trong database
SELECT USER_NAME(); -- 'dbo', 'john', etc.

-- SYSTEM_USER: Login name
SELECT SYSTEM_USER; -- 'DOMAIN\john'

-- IS_MEMBER: Kiểm tra membership trong role
SELECT IS_MEMBER('db_datareader'); -- 1 = Yes, 0 = No, NULL = lỗi

-- IS_ROLEMEMBER: Tương tự nhưng rõ ràng hơn
SELECT IS_ROLEMEMBER('db_owner', 'john_user');

-- HAS_PERMS_BY_NAME: Kiểm tra quyền
SELECT HAS_PERMS_BY_NAME('dbo.Orders', 'OBJECT', 'SELECT'); -- 1 nếu có quyền

-- Trong Function để dynamic security
CREATE FUNCTION dbo.GetSensitiveData(@RequestUserId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM SensitiveTable
    WHERE 
        IS_MEMBER('SensitiveDataViewers') = 1
        OR RequestedBy = @RequestUserId
);

10. Ví dụ So Sánh Toàn Diện

-- SCENARIO: Lấy discount percentage cho orderdetail

-- ❌ OPTION 1: Scalar UDF (tệ nhất)
CREATE FUNCTION dbo.GetDiscount_Scalar(@ProductId INT, @Quantity INT)
RETURNS DECIMAL(5,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(5,2);
    SELECT TOP 1 @Discount = DiscountRate
    FROM DiscountRules
    WHERE ProductId = @ProductId AND MinQuantity <= @Quantity
    ORDER BY MinQuantity DESC;
    RETURN ISNULL(@Discount, 0);
END;

SELECT od.*, dbo.GetDiscount_Scalar(od.ProductId, od.Quantity) AS Disc
FROM OrderDetails od; -- N lần gọi function = N lần query DiscountRules

-- ✅ OPTION 2: Inline TVF (tốt)
CREATE FUNCTION dbo.GetDiscount_iTVF(@ProductId INT, @Quantity INT)
RETURNS TABLE AS RETURN
(
    SELECT TOP 1 DiscountRate AS Discount
    FROM DiscountRules
    WHERE ProductId = @ProductId AND MinQuantity <= @Quantity
    ORDER BY MinQuantity DESC
);

SELECT od.*, d.Discount
FROM OrderDetails od
CROSS APPLY dbo.GetDiscount_iTVF(od.ProductId, od.Quantity) d;
-- Optimizer có thể join và optimize toàn bộ query!

-- ✅ OPTION 3: Set-based JOIN (tốt nhất)
SELECT 
    od.*,
    ISNULL(dr.DiscountRate, 0) AS Discount
FROM OrderDetails od
OUTER APPLY (
    SELECT TOP 1 DiscountRate
    FROM DiscountRules dr
    WHERE dr.ProductId = od.ProductId AND dr.MinQuantity <= od.Quantity
    ORDER BY dr.MinQuantity DESC
) dr;

Checklist Khi Viết Functions