SQL Functions

Level : Advanced

Mentor: Shailendra Chauhan

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

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.