Functions in SQL Server

Functions in SQL Server are essential components of Transact-SQL (T-SQL) that allow you to perform calculations, manipulate data, and streamline complex queries. They enable you to achieve a wide range of tasks efficiently, from simple data formatting to advanced computations and logic.

Introduction to Functions in SQL Server

Functions in SQL Server are reusable code blocks that accept input parameters, process them, and return a result. Functions enhance modularity, simplify complex logic, and make code reusable and maintainable.

Why Use Functions?

  • Simplify complex queries.
  • Avoid redundancy by reusing logic.
  • Ensure consistent calculations and results.

Types of Functions in SQL Server

SQL Server provides two primary categories of functions:

System Functions

Built-in functions provided by SQL Server to handle common operations like calculations, string manipulations, and date/time processing.

User-Defined Functions (UDFs)

Custom functions created by users to implement specific logic not covered by system functions.

Exploring System Functions

Scalar Functions

These return a single value and can be used wherever an expression is allowed.

Example: LEN()

				
					SELECT LEN('SQL Server') AS Length;

Explanation:

  • Returns the length of the string 'SQL Server'.

Output:

				
					Length
------
10

Aggregate Functions

These operate on a set of values and return a single aggregated value.

Example: AVG()

				
					SELECT AVG(salary) AS AverageSalary FROM employees;

Explanation:

  • Calculates the average value of the salary column.

Output: For salaries 5000, 7000, 9000:

				
					AverageSalary
-------------
7000

Ranking Functions

Used to assign a rank to rows within a result set.

Example: RANK()

				
					SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS Rank FROM employees;

Explanation:

  • Assigns ranks based on salary in descending order.

Output: For salaries 9000, 7000, 5000:

				
					name    salary    Rank
-----   -------   ----
Alice   9000      1
Bob     7000      2
Charlie 5000      3

String Functions

Used for manipulating string data.

Example: CONCAT()

				
					SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM employees;

Explanation:

  • Combines first_name and last_name with a space in between.

Output: For first_name = 'John', last_name = 'Doe':

				
					FullName
--------
John Doe

Date and Time Functions

Allow operations on date and time values.

Example: GETDATE()

				
					SELECT GETDATE() AS CurrentDateTime;

Explanation:

  • Returns the current system date and time.

Output:

				
					CurrentDateTime
---------------
2024-11-12 14:45:30.000

Conversion Functions

Used to convert data from one type to another.

Example: CAST()

				
					SELECT CAST(123.456 AS INT) AS ConvertedValue;

Explanation:

  • Converts the float 123.456 to an integer.

Output:

				
					ConvertedValue
--------------
123

User-Defined Functions (UDFs)

UDFs allow users to define custom logic.

Scalar UDFs

Returns a single value.

Example: Creating and Using a Scalar UDF

				
					CREATE FUNCTION CalculateTax(@amount FLOAT)
RETURNS FLOAT
AS
BEGIN
    RETURN @amount * 0.10;
END;

SELECT dbo.CalculateTax(1000) AS Tax;

Output:

				
					Tax
---
100.0

Inline Table-Valued Functions

Returns a table and is defined with a single SELECT statement.

Example:

				
					CREATE FUNCTION GetHighSalaryEmployees()
RETURNS TABLE
AS
RETURN
(
    SELECT name, salary FROM employees WHERE salary > 7000
);

SELECT * FROM dbo.GetHighSalaryEmployees();

Output: For employees with salaries 5000, 7000, 9000:

				
					name    salary
-----   ------
Alice   9000

Multi-Statement Table-Valued Functions

Returns a table but allows multiple statements for processing.

Example:

				
					CREATE FUNCTION GetEmployeesByDepartment(@dept_id INT)
RETURNS @Result TABLE (name NVARCHAR(50), salary FLOAT)
AS
BEGIN
    INSERT INTO @Result
    SELECT name, salary FROM employees WHERE department_id = @dept_id;
    RETURN;
END;

SELECT * FROM dbo.GetEmployeesByDepartment(1);

Output:

				
					name    salary
-----   ------
John    5000
Jane    7000

Examples and Outputs

Using Multiple Functions in a Query

				
					SELECT 
    UPPER(SUBSTRING(name, 1, 3)) AS ShortName, 
    ROUND(salary, 2) AS RoundedSalary,
    GETDATE() AS QueryDate 
FROM employees;

Output:

				
					ShortName   RoundedSalary   QueryDate
----------  -------------   -----------------------
JOH         5000.00         2024-11-12 14:50:45.000
JAN         7000.00         2024-11-12 14:50:45.000

Advanced Topics

Combining Functions

				
					SELECT LEN(CONCAT(first_name, ' ', last_name)) AS NameLength FROM employees;

Using Functions in WHERE Clauses

				
					SELECT * FROM employees WHERE ROUND(salary, -3) = 5000;

Best Practices for Using Functions

  1. Use Built-In Functions First: Prioritize system functions before creating UDFs.
  2. Avoid Functions in WHERE Clauses: This can impact query performance.
  3. Combine Judiciously: Avoid overly nested functions to maintain readability.

Functions in SQL Server are powerful tools that enhance data processing and simplify complex queries. Whether you're using built-in system functions or creating your own UDFs, understanding their capabilities will make you a more effective SQL developer. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources