SQL Server Topics

Introduction to SQL Server Functions

What are SQL Server Functions?

SQL Server Functions are database objects that accept parameters, perform an action (like calculations or data manipulation), and return a single value. They are similar to functions in programming languages. Functions can be used in SELECT statements, WHERE clauses, and other parts of your SQL queries to simplify complex logic and promote code reusability.

There are two main types of user-defined functions:

  • Scalar Functions: Return a single data value.
  • Table-Valued Functions (TVFs): Return a table data type.

Scalar Functions

Scalar functions return a single value of a specified data type. They are useful for encapsulating business logic or performing specific calculations.

Creating a Scalar Function

Here's an example of a scalar function that calculates the full name of a person by concatenating their first and last names:

CREATE FUNCTION dbo.GetFullName (@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

Using a Scalar Function

You can call this function in a SELECT statement:

Example Usage:

SELECT dbo.GetFullName('John', 'Doe');

This would return: John Doe

Scalar functions can also be used in queries against tables:

Example with Table Data:

-- Assuming you have a table named 'Employees' with FirstName and LastName columns
SELECT EmployeeID, dbo.GetFullName(FirstName, LastName) AS FullName
FROM Employees;

Table-Valued Functions (TVFs)

Table-valued functions return a result set that can be treated as a table. They are ideal for encapsulating complex queries or providing filtered/transformed data views.

There are two types of TVFs:

  • Inline Table-Valued Functions (ITVF): Contain only one statement, which is a SELECT statement. They are generally more performant than multi-statement TVFs.
  • Multi-Statement Table-Valued Functions (MSTVF): Can contain multiple statements, allowing for more complex logic before returning the table.

Creating an Inline Table-Valued Function

Example: A function that returns all employees from a specific department.

CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Email
    FROM Employees
    WHERE Department = @DepartmentName
);
GO

Using an Inline TVF

You can query an ITVF like a regular table:

Example Usage:

SELECT * FROM dbo.GetEmployeesByDepartment('Sales');

Built-in Functions

SQL Server also provides a rich set of built-in functions for various purposes, including:

  • Aggregate Functions: SUM(), AVG(), COUNT(), MAX(), MIN()
  • Scalar Functions: GETDATE(), LEN(), SUBSTRING(), CONVERT(), ISNULL()
  • String Functions: LEFT(), RIGHT(), REPLACE(), UPPER(), LOWER()
  • Date and Time Functions: DATEADD(), DATEDIFF(), YEAR(), MONTH(), DAY()
  • System Functions: @@ROWCOUNT, DB_NAME()

These functions can be directly used in your SQL queries without needing to be explicitly created.

Example with Built-in Functions:

SELECT
    COUNT(*) AS TotalEmployees,
    AVG(Salary) AS AverageSalary,
    GETDATE() AS CurrentDateTime
FROM Employees
WHERE Department = 'IT';

Best Practices

  • Use Scalar Functions Sparingly: While useful, excessive use of scalar functions in queries can sometimes impact performance, especially if they are not "deterministic" or if the query optimizer cannot effectively parallelize their execution.
  • Prefer Inline TVFs: For returning tabular data, inline TVFs are generally preferred over multi-statement TVFs due to better performance characteristics.
  • Keep Functions Focused: Design functions to perform a single, specific task. This improves readability and maintainability.
  • Consider Deterministic vs. Non-Deterministic: A deterministic function always returns the same result for the same input values. Non-deterministic functions may return different results even with the same inputs (e.g., functions that use system time). The optimizer can often use deterministic functions more effectively.