Browse Articles

Different Types of SQL Server Functions

08 Jan 2024
10 min read  

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 stored procedure and function refer to the article Difference between Stored Procedure and Functionin this SQL Course.

SQL Server Functions

SQL Server functions 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 SQL Server Scalar functions.

    System Scalar Function
    Scalar Function
    This returns an absolute number of the given number, which means 10.67.
    This will generate a random number of 10 characters.
    This will round off the given number to 3 places of decimal meaning 17.567
    This will return the upper case of the given string meaning '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 SQL Server Aggregate functions.

    System Aggregate Function
    Aggregate Function
    This returns the maximum value from a collection of values.
    This returns the minimum value from a collection of values.
    This returns an average of all values in a collection.
    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)
    Begin return (Select @FirstName + ' '+ @LastName);

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

    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


  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.


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.


Q1. How many types of commands are there in SQL Server?

SQL Commands are classified into five types based on the functions they perform: DDL (Data Definition Language), DML (Data Manipulation Language), DQL (Data Query Language), TCL (Transaction Control Language), and DCL (Data Control Language).

Q2. What is SQL Server full form?

Structured query language (SQL) is a programming language used to store and process data in relational databases. A relational database is a tabular database that stores information in tabular form, with rows and columns reflecting different data qualities and the various relationships between the data values.

Q3. What are the 3 categories of SQL?

Statements in Data Definition Language (DDL). Statements in Data Manipulation Language (DML). Statements of Transaction Control.

Q4. What are the three types of functions supported in Microsoft SQL Server?

In SQL Server, there are three categories of user-defined functions:
Scalar functions (those that return a single value)
Table Valued Functions (a single TSQL command that returns a Table Set)
Table Valued Functions with numerous Statements (contains numerous TSQL statements and returns a Table Set)

Q5. What are different types of SQL functions explain with example?

SQL scalar functions are built-in or user-defined functions that take one or more parameters and return a single value. SQL character functions are scalar functions that are used to manipulate and convert character data, such as strings. SQL functions are classified into two types: aggregate functions and scalar functions.
Share Article
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.
Learn to Crack Your Technical Interview

Accept cookies & close this