In Focus

Functions In SQL Server

This article explains how to create functions in SQL Server using the SQL function operator.

Functions in SQL


Functions are a very important concept in SQL Server databases. In this article, I will explain how to create and use a function in SQL Server. 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 create by a user.
  2. System Defined Function: System functions are built in database functions. 
SQL Server database

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.
 
Create One Table 
 

User Defined Functions

 
SQL Server support 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.
  1. Create function Fun_EmployeeInformation()      
  2. returns table       
  3. as      
  4. return(select * from Employee  )     
Table Valued Function

Now see a newly created function in the database.

see crated function in databas

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

Getting Employee Table Information

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:
  1. create function fun_JoinEmpColumnInfo  
  2. (  
  3.    @EmpContact nchar(15),  
  4.    @EmpEmail nvarchar(50),  
  5.    @EmpCity varchar(30)  
  6. )  
  7. returns nvarchar(100)  
  8. as  
  9. begin return(select @EmpContact+ ' ' +@EmpEmail + ' ' + @EmpCity)  
  10. end  
Scalar function

Now see a create scalar function in the database.

database

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

created scalar function

System function

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

System function

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

This function operates on employee salary records.

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

Command
  1. select max(salary) as Salary from employee  
select max

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

Command
  1. select min(salary) as Salary from employee  
select min

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

Command
  1. select sum(salary) as Salary from employee  
select sum

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.
  1. create function fun_PrintNumber()  
  2. returns decimal(7,2)  
  3. as  
  4. begin  
  5.     return 1000.13  
  6. end  
create function

Now call a function and display the stored record using the print command.
  1. print dbo.fun_PrintNumber()  
call a function

Now one more mathematical function to create a two-number addition.
  1. CREATE FUNCTION Fun_Addition(@Num1 Decimal(7,2),  
  2.                          @Num2 Decimal(7,2))  
  3. RETURNS Decimal(7,2)  
  4. Begin  
  5.     DECLARE @Result Decimal(7,2)  
  6.     SET @Result = @Num1 + @Num2  
  7.     RETURN @Result  
  8. end   
mathematical function

Now call an addition function as in the following:
  1. print dbo.Fun_Addition(12,13)  
addition function

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