Scalar user-defined functions :

Reuse of code is one of the fundamental principles we learn when programming in any language, and the SQL language is no exception. It provides many means by which to logically group code and reuse it.

One such means in SQL Server is the scalar user-defined function. It seems so convenient to hide away all those complex calculations in a function, and then simply invoke it in our queries. However, the hidden "sting in the tail" is that it can bring a heavy toll in terms of performance. When used in a query, scalar functions are evaluated for each row and, with large tables, this can result in very slow running queries. This is especially true when the scalar function needs to access another table to retrieve data.

Here is one example. Given tables with products and sales for products, the request is to retrieve total sales per product. Since the total sales value can be reused in another place, you decide to use a scalar function to calculate the total sales for a product:

CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS DECIMAL(15, 2)
AS
BEGIN
RETURN(SELECT SUM(sale_amount)
FROM Sales
WHERE sku = @sku);
END

Then the query to retrieve the total sales for each product will look like this;

SELECT sku, product_description, dbo.GetTotalSales(sku) AS total_sales
FROM Products;

Isn't this a very neat and good looking query? But just wait until you run it over a large data set. The total sales calculation will be repeated for each and every row, and the overhead will be proportional to the number of rows. The correct way to handle this is, if possible, is to rewrite the function as a table-valued function, or simply perform the calculation in the main query. In our example, performing the calculation in the query will look like this:

SELECT P.sku, P.product_description, SUM(S.sale_amount) As total_sales
FROM Products AS P
JOIN Sales AS S
ON P.sku = S.sku
GROUP BY P.sku, P.product_description;

And here is a table-valued function that can be used to calculate total sales:

CREATE FUNCTION dbo.GetTotalSales(@sku INT)
RETURNS TABLE
AS
RETURN(SELECT SUM(sale_amount) AS total_sales
FROM Sales
WHERE sku = @sku);

Now the table-valued function can be invoked in the query using the APPLY operator:

SELECT sku, product_description, total_sales
FROM Products AS P
CROSS APPLY dbo.GetTotalSales(P.sku) AS S;