Different Types of SQL Server Functions

Different Types of SQL Server Functions

14 Mar 2024
Intermediate
556K Views
15 min read
Learn via Video Course & by Doing Hands-on Labs

SQL Server Course

Different Types of SQL Server Functions: An Overview

Explore various functions such as user-defined and system functions in this SQL Server tutorial to improve your database querying skills. A function is a database object in SQL Server. It is a set of SQL statements that accept only input parameters, perform actions, and return the result. For more about the stored procedure and function refer to the article Difference between Stored Procedure and Function in this SQL Course.

What is Function in SQL Server?

Function in SQL Server are database objects that include a group of SQL statements to perform a specified activity. A function takes parameters, performs actions, and returns the outcome. It should be noted that functions always return either a single value or a table.

Rules for creating SQL Server Functions

The rules for writing SQL Server functions are as follows:

  • A function must be given a name, which cannot begin with a special character such as @, $, #, or other similar characters.
  • The only statements that work with functions are SELECT statements.
  • AVG, COUNT, SUM, MIN, DATE, and other functions can be used anywhere with the SELECT query in SQL.
  • When a function is invoked, it compiles.
  • Functions are required to return a value or result.
  • Only input parameters are used by functions.
  • TRY & CATCH statements are not permitted in functions.

Types of Function

SQL Server divides functions into two types:

  1. System Functions
  2. User-Defined Functions

1. System Defined Function

These functions are defined by SQL Server for different purposes. The functions that are defined by the system are known as "system-defined functions". In other words, all the built-in functions supported by the SQL server are referred to as system-defined functions. Usage of the built-in functions saves much development time while performing certain tasks. These types of functions generally work with the SQL select statement to calculate the values and the manipulated data. We have two types of system-defined functions in the SQL Server

  1. Scalar Function

    Scalar functions operate on a single value and return a single value. Below is a list of some useful Scalar Functions in SQL Server.

    System Scalar Function
    Scalar Function
    Description
    abs(-10.67)
    This returns an absolute number of the given number, which means 10.67.
    rand(10)
    This will generate a random number of 10 characters.
    round(17.56719,3)
    This will round off the given number to 3 places of decimal meaning 17.567
    upper('dotnet')
    This will return the upper case of the given string meaning 'DOTNET'
    lower('DOTNET')
    This will return the lowercase of the given string means 'dotnet'
    ltrim(' dotnet')
    This will remove the spaces from the left-hand side of the 'dotnet' string.
    convert(int, 15.56)
    This will convert the given float value to integer means 15.
  2. Aggregate Function

    Aggregate functions operate on a collection of values and return a single value. Below is a list of some useful Aggregate Functions in SQL Server.

    System Aggregate Function
    Aggregate Function
    Description
    max()
    This returns the maximum value from a collection of values.
    min()
    This returns the minimum value from a collection of values.
    avg()
    This returns an average of all values in a collection.
    count()
    This returns no of counts from a collection of values.

2. User-Defined Function

User-Defined Functions (UDFs) are user-created functions that encapsulate specialized logic for use within SQL Server. They accept input, perform operations, and return results, hence expanding database capabilities beyond built-in functions. These functions are created by the user in the system database or a user-defined database. We have three types of user-defined functions.

  1. Scalar Function

    The user-defined scalar function also returns a single value due to actions performed by the function. We return any datatype value from a function.

    --Create a table 
    CREATE TABLE Employee
    (
     EmpID int PRIMARY KEY,
     FirstName varchar(50) NULL,
     LastName varchar(50) NULL,
     Salary int NULL,
     Address varchar(100) NULL,
    )
    --Insert Data
    Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
    Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
    Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
    Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
    --See created table
    Select * from Employee 

    Creates the "Employee" table, which has columns for employee ID, first name, last name, salary, & address.Inserts data for four employees into the table, including their contact information. All data from the "Employee" database is retrieved and shown.

    User-Defined Function -  Scaler Function
    --Create function to get emp full name 
    Create function fnGetEmpFullName
    (
     @FirstName varchar(50),
     @LastName varchar(50)
    )
    returns varchar(101)
    As
    Begin return (Select @FirstName + ' '+ @LastName);
    end 

    The function "fnGetEmpFullName" is created, which accepts first and last names as input. Concatenates the first and last names, separated by a space, to make the whole name. The created full name is returned as a varchar(101) string.

    User-Defined Function -  Scaler Function
    --Calling the above created function
    Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 

    Full names are obtained by invoking the "fnGetEmpFullName" function and giving first and last names from the "Employee" table. The generated full names are renamed "Name" for clarity. Retrieves each employee's "Salary" column. Displays the combined results, including full names and salaries.

    User-Defined Function -  Scaler Function
  2. Inline Table-Valued Function

    The user-defined inline table-valued function returns a table variable due to actions performed by the function. The value of the table variable should be derived from a single SELECT statement.

    --Create function to get employees
    Create function fnGetEmployee()
    returns Table
    As
     return (Select * from Employee) 

    This query creates a function called "fnGetEmployee" that retrieves all data (marked by the symbol "*") from the "Employee" table. Returns the obtained data as a table, making it available for use in subsequent queries or actions.

    User-Defined Function -  Inline Table-Valued Function
    --Now call the above created function
    Select * from fnGetEmployee() 

    The previously declared "fnGetEmployee" function is invoked, which retrieves all employee data. Treats the returned data as a table, allowing for direct selection of all columns with "*". Displays all employee information retrieved from the function's output.

    User-Defined Function -  Inline Table-Valued Function
  3. Multi-Statement Table-Valued Function

    A user-defined multi-statement table-valued function returns a table variable due to actions performed by the function. In this, a table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.

    --Create function for EmpID,FirstName and Salary of Employee
    Create function fnGetMulEmployee()
    returns @Emp Table
    (
    EmpID int, 
    FirstName varchar(50),
    Salary int
    )
    As
    begin
     Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
    --Now update salary of first employee
     update @Emp set Salary=25000 where EmpID=1;
    --It will update only in @Emp table not in Original Employee table
    return
    end 

    Creates a function called "fnGetMulEmployee" that returns a custom table variable called "@Emp" that has columns for employee ID, first name, and salary. Fills the "@Emp" table with data from the "Employee" table. Modify the salary of the first employee in the "@Emp" database, but without affecting the original "Employee" table, guaranteeing data integrity. As the function's output, it returns the changed "@Emp" table.

    User-Defined Function -  Multi-Statement Table-Valued Function
    --Now call the above created function
    Select * from fnGetMulEmployee() 

    This query uses the "fnGetMulEmployee" method to retrieve a custom table with employee IDs, first names, and salaries, with the first employee's pay modified to 25000.

    User-Defined Function -  Multi-Statement Table-Valued Function
    --Now see the original table. This is not affected by above function update command
    Select * from Employee 

    This query gets & displays the original "Employee" table, confirming that its data is unaltered because function modifications were limited to the temporary table.

    User-Defined Function -  Multi-Statement Table-Valued Function

Difference between Function and Procedure in SQL Server

FeatureFunctionProcedure
Basic principles Functions use the input provided to calculate the output of a program.Procedures use the inputs provided to determine which tasks to complete in what order.
Try-Catch BlocksThe try-catch Blocks are not supported by functions.Try-catch Blocks are supported by procedures.
SQL QueryWithin an SQL query, we can invoke a function. An SQL query cannot call a procedure.
SELECT  Function calls may be present in the SELECT statements. Procedure calls cannot ever be made in the SELECT statements.
ReturnTo the caller function or code, a function would return the returned value or control.In contrast, a procedure would return control but neither the caller function nor the code would receive any value from it.
Statements in DMLDML statements cannot be used in a function (this includes insert, delete, and update functions). The DML statements are always available in the event of a procedure.
Call  A procedure can be used to call a function.No function can be used to call a procedure.
CompilationWhen we call a function within a program, it gets compiled. The procedures must be compiled once, but if more than one is needed, they can be called repeatedly; a compilation is not required each time.
ExpressionExpressions must be handled by a function. Expressions do not have to be handled by a process.

Note

  1. Unlike the Stored Procedure, the Function returns only a single value.

  2. Unlike the Stored Procedure, the Function accepts only input parameters.

  3. Unlike the Stored Procedure, the Function is not used to Insert, Update, or Delete data in a database table(s).

  4. Like the Stored Procedure, the Function can be nested up to 32 levels.

  5. User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.

  6. User Defined Function can't return XML Data Type.

  7. User Defined Function doesn't support Exception handling.

  8. User Defined Function can call only Extended Stored Procedure.

  9. User Defined Function doesn't support set options like set ROWCOUNT etc.

Read More:

Summary

SQL Server functions extend the capabilities of databases by encapsulating logic, accepting input, conducting actions, and returning results. They are classified as either system-defined or user-defined, with variations within each kind to meet unique requirements.

FAQs

Q1. Which three categories of SQL Server functions are there?

In SQL Server, user-defined functions come in three different varieties:
  • Scalar: Functions That Return A Single Value. 
  • Inline Table Valued Functions: These functions return a Table Set with just one TSQL statement.
  • Multiple Statements Table Valued Functions: (Returns Table Set; contains many TSQL statements).

Q2. What is the purpose of any SQL Server function?

When a value from the first table matches every value from the second table, SQL ANY returns the row with the matching value.

Q3. How many SQL functions are there?

Aggregate functions and scalar functions are the two categories under which SQL functions fall.

Q4. How can I locate SQL functions?

You can list functions in the current database or in a different database by using the SHOW FUNCTIONS command. The names of each function and the identity of the function definer are returned by the function list.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Full Stack .Net Developer Jun 30 SAT, SUN
Filling Fast
11:00AM to 01:00PM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this