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

Types of SQL functions SQL Server

What types of SQL functions in SQL Server

Answers (2)