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

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

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.

Types of SQL functions SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)