Answer.

In SQL, business logic can be implemented through User Defined Functions (UDF), which allows part of the calculations and business rules to be moved into the database.

Scalar functions return a single value and are called in expressions — for example, to calculate a total based on parameters:

CREATE FUNCTION dbo.GetDiscount(@price DECIMAL(10,2), @loyaltyLevel INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @price * CASE 
        WHEN @loyaltyLevel = 1 THEN 0.95
        WHEN @loyaltyLevel = 2 THEN 0.90
        ELSE 1.0
    END
END;
-- Example usage:
SELECT Name, dbo.GetDiscount(Price, LoyaltyLevel) AS DiscountPrice FROM Products;

Table-valued functions return a set of rows (a table):

CREATE FUNCTION dbo.ActiveOrders(@userId INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Orders WHERE UserId = @userId AND Status = 'Active'
);
-- Usage:
SELECT * FROM dbo.ActiveOrders(123);

Differences, limitations, and recommendations:

Scalar UDFs often have low performance in large datasets, as they are called row by row;
Table-valued UDFs integrate better into query plans and can be used like regular tables;
Logic that requires access to external resources or complex business processing is better moved outside of SQL.

Tricky question.

What is the difference between an INLINE table function and a multi-statement table function? How does this choice affect performance?

Answer and example:

An INLINE function (single-statement, immediately returns a SELECT) is optimized as part of the main query, does not create an additional table, and works faster, the query plan is shared.
A multi-statement function creates a temporary table (table variable), which usually leads to a worse query plan, potential cardinality estimation errors, and a drop in performance.

-- INLINE
CREATE FUNCTION dbo.InlineActiveOrders(@userId INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Orders WHERE UserId = @userId AND Status = 'Active'
);

-- Multi-statement
CREATE FUNCTION dbo.MultiActiveOrders(@userId INT)
RETURNS @result TABLE (...)
AS
BEGIN
    INSERT INTO @result SELECT ...
    -- logic
    RETURN
END

Case Study

Project: Financial Analytics. We used scalar UDFs in the select part of a report query to a large array of tables: SELECT Amount, dbo.CalcTax(Amount, Type) FROM Transactions. The query took 5-10 minutes due to row-by-row processing of the UDF. We rewrote it using an embedded CASE: the time was reduced to seconds.

Case Study

Project: E-commerce. To find user carts, we used multi-statement table functions, where there was filtering logic over several steps. It turned out that SQL does not build an optimal plan and scans, even when a single element is needed. Replacing it with an INLINE function immediately sped up queries by 50 times.

Case Study

Project: CRM. We moved the business logic for bonus accrual into a complex UDF. After a couple of months, we realized that the business formulas had changed, and updating the function was complicated due to its tight coupling with the data. In an iterative product, the UDF led to high coordination costs for changes between the backend and the database.

How to implement complex business rules and calculations directly in SQL using User Defined Functions (UDF)? What are the differences and limitations between scalar and table-valued UDFs, when is it advisable to use them, and when is it better to implement such logic in an external application?

Answer.

Tricky question.