SQL Server Functions for Beginners - Scalar and Table-Valued Functions

Functions in SQL Server are reusable pieces of code that can accept parameters and return values. In this beginner's guide, we'll explore two main types of functions - Scalar Functions and Table-Valued Functions - and how to use them effectively using SQL code examples.

Scalar Functions

Scalar functions return a single value. They are commonly used for calculations and transformations. Here's an example of creating and using a scalar function:

-- Create a scalar function
CREATE FUNCTION dbo.CalculateTax (@Amount DECIMAL(10, 2), @TaxRate DECIMAL(4, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @Amount * @TaxRate;
END;
-- Use the scalar function
SELECT dbo.CalculateTax(100, 0.1) AS TaxAmount;
    

Table-Valued Functions

Table-Valued Functions return a table-like result set. They are useful for complex queries and can accept parameters. Here's an example of creating and using a table-valued function:

-- Create a table-valued function
CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE Department = @DepartmentName
);
-- Use the table-valued function
SELECT * FROM dbo.GetEmployeesByDepartment('HR');
    

Using Functions in SQL Statements

You can use functions in various SQL statements. For example, in a SELECT statement, you can apply scalar functions to columns:

SELECT FirstName, LastName, dbo.CalculateTax(Salary, 0.1) AS TaxAmount
FROM Employees;
    

And for table-valued functions, you can use them in JOIN operations and subqueries to retrieve specific data.

What's Next?

You've learned the basics of scalar and table-valued functions in SQL Server as a beginner. To become proficient, you can explore more advanced topics such as error handling within functions, optimizing function performance, and best practices for designing and using functions in your SQL Server databases.

Functions are valuable tools for encapsulating and reusing logic in SQL Server database applications.