SQL Server Functions 

In SQL, functions are reusable blocks of code that perform a specific operation and return a value. They can be divided into two main types: Scalar Functions and Aggregate Functions. 

Here each method is shared with examples.

Scalar Functions

The scalar function returns a value based on the input value. It can be built-in or user-defined.

Common Built-in Scalar Functions

UPPER()- It converts a string to uppercase.

SELECT UPPER('hello') AS UppercaseString;  -- Output: 'HELLO'

LOWER()- It converts a string to lowercase.

SELECT LOWER('WORLD') AS LowercaseString;  -- Output: 'world'

LEN()- It returns the length of a string.

SELECT LEN('SQL Server') AS StringLength;  -- Output: 11

GETDATE()- It returns the current date and time.

SELECT GETDATE() AS CurrentDateTime;  -- Output: Current date and time

ROUND()- Rounds a numeric value to a specified number of decimal places.

SELECT ROUND(123.4567, 2) AS RoundedValue;  -- Output: 123.46

User-Defined Scalar Functions

You can create your own scalar functions. Here is an example that calculates the square of a number.

CREATE FUNCTION dbo.Square(@Number INT)
RETURNS INT
AS
BEGIN
   RETURN @Number * @Number;
END;

Execute-

SELECT [dbo].[Square](5) AS SquaredValue; 
 -- Output: 25

Aggregate Functions

The aggregation function works on a range of values ​​and returns a single summary value. It is often used with the GROUP BY clause.

Common Aggregate Functions

COUNT()- it returns the number of rows in a group or table.

SELECT COUNT(*) AS TotalEmployees FROM Employees;  
-- Count of all employees

SUM()- It returns the sum of a numeric column.

SELECT SUM(Salary) AS TotalSalary FROM Employees;  
-- Sum of all salaries

AVG()- It returns the average of a numeric column

SELECT AVG(Salary) AS AverageSalary FROM Employees;  
-- Average salary

MIN()- It returns the minimum value in a set.

SELECT MIN(Salary) AS LowestSalary FROM Employees;  
-- Lowest salary

MAX()- It returns the maximum value in a set.

SELECT MAX(Salary) AS HighestSalary FROM Employees;  
-- Highest salary

Table-Valued Functions

Table-valued functions return a table as a result. These can be used in queries such as regular tables.

Example
Here is an example that returns employees from a specific department.

CREATE FUNCTION dbo.GetEmployeesByDeptID(@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
   SELECT * FROM Employees WHERE DepartmentID = @DepartmentID
);

Execute-

SELECT * FROM [dbo].[GetEmployeesByDeptID](1);

Summary of Function Types

Type	Description	Example
Scalar Functions	Return a single value	UPPER('text')
Aggregate Functions	Return a summary value	SUM(Salary)
Table-Valued Functions	Return a table	GetEmployeesByDepartment(1)

SQL processing is necessary to perform various tasks efficiently. They help preserve logic and encourage code reuse, making database operations more efficient and consistent.

Also, Read: Explain the SQL Stored Procedures