What is the difference between a scalar-valued and a table-valued user-defined function?

In SQL Server, user-defined functions (UDFs) are custom functions that can be created by users. They can be of two types - scalar-valued and table-valued. The difference between them lies in their purpose, return value, and usage.

A scalar-valued UDF returns a single scalar value (e.g. integer, string, etc.) as its result, based on the input parameters passed to it. It can be used in SELECT, WHERE or any other clause that requires a single value. For example, consider the following scalar-valued UDF that returns the length of a string input:

CREATE FUNCTION fn_GetStringLength(@str VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @len INT
    SET @len = LEN(@str)
    RETURN @len
END

This UDF takes a string input parameter and returns its length as an integer value. It can be called in a query like this:

SELECT fname, lname, dbo.fn_GetStringLength(fname) AS fname_length
FROM employees
WHERE dbo.fn_GetStringLength(fname) > 5;

In this example, we use the scalar-valued UDF to calculate the length of the ’fname’ column and use it in a WHERE clause to filter the results.

On the other hand, a table-valued UDF returns a table as its result, based on the input parameters passed to it. It can be used in FROM clause or JOIN clauses, like any other table. For example, consider the following table-valued UDF that returns all the products whose price is greater than a specified value:

CREATE FUNCTION fn_GetProductsByPrice(@price DECIMAL(18,2))
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM products
    WHERE price > @price
);

This UDF takes a decimal input parameter and returns a table containing all the products whose price is greater than that value. It can be used in a query like this:

SELECT * FROM dbo.fn_GetProductsByPrice(100);

In this example, we use the table-valued UDF to get all the products whose price is greater than 100.

In summary, a scalar-valued UDF returns a single scalar value while a table-valued UDF returns a table. Scalar-valued UDFs are used in SELECT, WHERE, and other clauses that require a single value, whereas table-valued UDFs are used in FROM and JOIN clauses as if they were tables.