SQL Functions
Duration : 00:02:00
System Functions
System functions in SQL Server provide information about the server or database environment. For example, the @@VERSION function returns the current SQL Server version.Example
SELECT @@VERSION AS SQL_Server_Version;
String Functions
String functions manipulate text data. The LEN function returns the length of a string.Example
SELECT LEN('Hello, World!') AS String_Length;
Date and Time Functions
Date and time functions help with date and time calculations. The GETDATE function returns the current date and time.Example
SELECT GETDATE() AS Current_DateTime;
Aggregate Functions
Aggregate functions perform calculations on sets of values. The SUM function calculates the sum of a column.Example
SELECT SUM(Salary) AS Total_Salary FROM Employees;
Mathematical Functions
Mathematical functions perform mathematical operations. The SQRT function calculates the square root of a number.Example
SELECT SQRT(25) AS Square_Root;
Ranking Functions
Ranking functions assign a rank to rows in a result set. The ROW_NUMBER function assigns a unique number to each row.Example
SELECT Name, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;
User-Defined Functions
User-defined functions are custom functions created by users. Here's an example of a scalar user-defined function called AddNumbers that adds two integers.Example
CREATE FUNCTION AddNumbers(@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
RETURN @num1 + @num2;
END;
SELECT dbo.AddNumbers(5, 7) AS Sum;
Table-Valued Functions
Table-valued functions return a table as a result. There are two types: inline and multi-statement table-valued functions.Inline Table-Valued Functions
Inline TVFs return a table inline within a query.Example
CREATE FUNCTION GetEmployeesByDepartment(@deptID INT)
RETURNS TABLE
AS
RETURN (
SELECT * FROM Employees WHERE DepartmentID = @deptID
);
SELECT * FROM GetEmployeesByDepartment(1);
Multi-Statement Table-Valued Functions (MSTVF)
MSTVFs are more complex and involve multiple SQL statements.Example
CREATE FUNCTION GetHighSalaryEmployees()
RETURNS @ResultTable TABLE (
EmployeeID INT,
Name NVARCHAR(50),
Salary DECIMAL(10, 2)
)
AS
BEGIN
INSERT INTO @ResultTable
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 50000;
RETURN;
END;
SELECT * FROM GetHighSalaryEmployees();
Still have some questions? Let's discuss.
CONTACT US