Functions In SQL Server: A Beginner's Guide


In this article, we will learn about SQL Server Functions with examples.

SQL Server Functions

SQL Server Functions are useful objects in SQL Server databases. A SQL Server function is a code snippet that can be executed on a SQL Server. In this article, I will explain how to create and use a function in SQL Server.

Function Category Function Name Description
Aggregate COUNT() Returns the number of rows in a result set.
  SUM() Calculates the sum of values in a column.
  AVG() Computes the average of values in a column.
  MIN() Returns the minimum value in a column.
  MAX() Returns the maximum value in a column.
  GROUP_CONCAT() Concatenates strings from a group into one string.
String CONCAT() Concatenates two or more strings.
  LEN() Returns the length of a string.
  UPPER() / UCASE() Converts a string to uppercase.
  LOWER() / LCASE() Converts a string to lowercase.
  LEFT() Extracts a specified number of characters from the beginning of a string.
  RIGHT() Extracts a specified number of characters from the end of a string.
Date and Time GETDATE() Returns the current system date and time.
  DATEPART() Extracts a specific part of a date/time.
  DATEADD() Adds a specified interval to a date/time.
  DATEDIFF() Calculates the difference between two dates/times.
  YEAR() Extracts the year from a date.
  MONTH() Extracts the month from a date.
  DAY() Extracts the day of the month from a date.
Conversion CAST() Converts data from one data type to another.
  CONVERT() Converts data from one data type to another, with specific formatting.
Conditional CASE Performs conditional logic in SQL queries.
  COALESCE() Returns the first non-null value from a list of expressions.
  NULLIF() Returns NULL if two expressions are equal; otherwise, returns the first expression.
  IIF() Returns one of two values based on a Boolean expression.
Mathematical ROUND() Rounds a numeric value to a specified precision.
  CEILING() Rounds up to the nearest integer.
  FLOOR() Rounds down to the nearest integer.
  ABS() Returns the absolute value of a numeric expression.
  POWER() Raises a number to a specified power.
  SQRT() Returns the square root of a number.
  RAND() Generates a random number between 0 and 1.

In T-SQL, a function is considered an object. Here are some of the rules when creating functions in SQL Server.

  1. A function must have a name and a function name can never start with a special character such as @, $, #, and so on.
  2. Functions only work with select statements.
  3. Functions can be used anywhere in SQL, like AVG, COUNT, SUM, MIN, DATE and so on with select statements.
  4. Functions compile every time.
  5. Functions must return a value or result.
  6. Functions only work with input parameters.
  7. Try and catch statements are not used in functions.

SQL Server Function Types

SQL Server supports two types of functions - user-defined and system.

  1. User-Defined function: User-defined functions are created by a user.
  2. System Defined Function: System functions are built-in database functions. 

Functions In SQL Server

Before we create and use functions, let's start with a new table. 

Create a table in your database with some records. Here is my table.

Functions In SQL Server

User-Defined Functions

SQL Server supports two types of user-defined functions:

  1. Table-Valued Functions
  2. Scalar Valued Functions

Table-Valued Functions

In this type of function, we select a table data using a user-created function. A function is created using the Create function SQL command. The following query creates a new user-defined function.

Create function Fun_EmployeeInformation()      
returns table       
return(select * from Employee  )


Table Valued Functions

Now see a newly created function in the database.

Functions In SQL Server

Now get Employee table information with the created function Fun_EmployeeInformation() using a select statement.

Functions In SQL Server

Scalar function

Now we are getting an Employee table with two different data joined and displayed in a single column data row. Here create a two-column join function as in the following:

create function fun_JoinEmpColumnInfo  
   @EmpContact nchar(15),  
   @EmpEmail nvarchar(50),  
   @EmpCity varchar(30)  
returns nvarchar(100)  
begin return(select @EmpContact+ ' ' +@EmpEmail + ' ' + @EmpCity)  

Scalar function

Now see a create a scalar function in the database.

Functions In SQL Server

Now the created scalar function is used for displaying Employee info in one column data row as in the following:

create SQL scalar function

System function

This function is used for inserting records and is a system built-in function.

SQL System function

Here provide some Aggregate basic function examples with our Employee Table.

This function operates on employee salary records.

Getting the highest salary record with the max() function as in the following.


select max(salary) as Salary from employee  

Functions In SQL Server

Getting the lowest salary record with the min() function as in the following.


select min(salary) as Salary from employee  

Functions In SQL Server

Count the total salary with the sum() function as in the following.


select sum(salary) as Salary from employee  

Functions In SQL Server

We are showing a basic example of how to use a system function. Many more functions are available in the database.

Now we will show one more example of how to store data using a function and display that stored data using a SQL print command.

create function fun_PrintNumber()  
returns decimal(7,2)  
    return 1000.13  

Functions In SQL Server

Now call a function and display the stored record using the print command.

print dbo.fun_PrintNumber()  

Functions In SQL Server

Now one more mathematical function to create a two-number addition.

CREATE FUNCTION Fun_Addition(@Num1 Decimal(7,2),  
                         @Num2 Decimal(7,2))  
RETURNS Decimal(7,2)  
    DECLARE @Result Decimal(7,2)  
    SET @Result = @Num1 + @Num2  
    RETURN @Result  

Functions In SQL Server

Now call an additional function as in the following:

print dbo.Fun_Addition(12,13)

Functions In SQL Server

Watch here a full video for more information about MS SQL Functions, Syntaxes, Tips & Tricks.


In this article, I explained functions in SQL Server. I hope you understand about functions and how they work in SQL Server databases.

Similar Articles