Types of SQL functions SQL Server

Question

What types of SQL functions in SQL Server

Vishal Yelve · Answer

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);

Sandhiya Priya · Answer

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.

Cynthia Sathuragiri · Answer

In SQL Server, functions are of two main types: System functions – built-in, like string ( LEN ), numeric ( ABS ), date ( GETDATE ), aggregate ( SUM ), ranking ( ROW_NUMBER ), etc. User-defined functions (UDFs) – created by you, either scalar (return one value) or table-valued (return a table).

Types of SQL functions SQL Server

Answers (3)