Types of SQL functions SQL Server

Answer: Types of SQL Functions in SQL Server

In SQL Server, functions are routines that accept parameters, perform an action (such as a calculation or data transformation), and return a single value or a table.

They are mainly divided into two broad categories:

1. System Functions

These are built-in functions provided by SQL Server.

Types of System Functions:

• Scalar Functions – Return a single value (e.g., GETDATE(), LEN(), UPPER()).

• Aggregate Functions – Perform calculations on a set of values and return one value (e.g., SUM(), AVG(), COUNT()).

• Ranking Functions – Assign a ranking number to each row in a result set (e.g., ROW_NUMBER(), RANK(), DENSE_RANK()).

• String Functions – Work with character data (e.g., LEFT(), RIGHT(), CHARINDEX()).

• Date & Time Functions – Handle date/time operations (e.g., DATEADD(), DATEDIFF(), GETUTCDATE()).

• Mathematical Functions – Perform numeric calculations (e.g., ABS(), ROUND(), POWER()).

• Conversion Functions – Convert one data type to another (e.g., CAST(), CONVERT()).

• Logical Functions – Return values based on conditions (e.g., IIF(), CHOOSE()).

2. User-Defined Functions (UDFs)

These are functions you create yourself in SQL Server.

Types of UDFs:

• Scalar User-Defined Functions – Return a single value.

  CREATE FUNCTION dbo.GetFullName(@First NVARCHAR(50), @Last NVARCHAR(50))
  RETURNS NVARCHAR(101)
  AS
  BEGIN
      RETURN @First + ' ' + @Last
  END
  

• Inline Table-Valued Functions (iTVF) – Return a table using a single SELECT statement.

• Multi-Statement Table-Valued Functions (mTVF) – Return a table using multiple statements inside the function body.

 Key Points:

• Functions must return a value (scalar or table).

• Unlike stored procedures, functions cannot modify database state (no INSERT, UPDATE, DELETE on permanent tables).

• Functions can be reused in queries for consistency and simplicity.

 In short:
SQL Server functions are either built-in (system) or custom (user-defined), and they help you encapsulate reusable logic for calculations, data transformation, or returning result sets.

In SQL Server, functions are of two main types:

1. System functions – built-in, like string (LEN), numeric (ABS), date (GETDATE), aggregate (SUM), ranking (ROW_NUMBER), etc.

2. User-defined functions (UDFs) – created by you, either scalar (return one value) or table-valued (return a table).

System Functions and User-Defined Functions (UDFs).

1. System Functions (Built-in by SQL Server)

These come ready-made and fall into different groups:

Type	Purpose	Example
Scalar Functions	Return a single value	GETDATE(), LEN('Hello')
Aggregate Functions	Return a single value from multiple rows	SUM(Price), COUNT(*)
Ranking Functions	Provide ranking numbers	ROW_NUMBER(), RANK()
String Functions	Manipulate strings	SUBSTRING(), REPLACE()
Date & Time Functions	Work with date/time	DATEADD(), DATEDIFF()
Mathematical Functions	Perform calculations	ROUND(), ABS()
Logical Functions	Conditional logic	IIF(), CHOOSE()
Conversion Functions	Change data types	CAST(), CONVERT()
Configuration Functions	Return system info	@@VERSION, @@SERVERNAME

2. User-Defined Functions (UDFs) (Created by you)

These are written using CREATE FUNCTION and can be:

A. Scalar Functions

• Return a single value (string, number, date, etc.).

• Can be used in SELECT or WHERE.

CREATE FUNCTION dbo.GetFullName (@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @First + ' ' + @Last
END

Usage:

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

B. Inline Table-Valued Functions (iTVF)

• Return a table.

• Faster than multi-statement because they work like a parameterized view.

CREATE FUNCTION dbo.GetOrdersByCustomer(@CustomerId INT)
RETURNS TABLE
AS
RETURN (
    SELECT OrderID, OrderDate, Total
    FROM Orders
    WHERE CustomerID = @CustomerId
);

Usage: 

SELECT * FROM dbo.GetOrdersByCustomer(5);

C. Multi-Statement Table-Valued Functions (mTVF)

• Return a table but with multiple statements inside.

• More flexible but slightly slower than iTVF.

CREATE FUNCTION dbo.GetRecentOrders(@Days INT)
RETURNS @Result TABLE (
    OrderID INT,
    OrderDate DATE,
    Total DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @Result
    SELECT OrderID, OrderDate, Total
    FROM Orders
    WHERE OrderDate >= DATEADD(DAY, -@Days, GETDATE());

    RETURN;
END

Usage:

SELECT * FROM dbo.GetRecentOrders(30);