In this article, we will understand User Defined Function in sql server with different types of function in sql server a) Scalar function b) Inline table-valued functions c) Multi-statement table-valued functions
User-defined Functions In SQL Server
User-defined functions also called UDF. In SQL server there are 3 different types of User-Defined functions
- Scalar function.
- Inline table-valued functions.
- Multi-statement table-valued functions.
You can find these functions folder in SQL Server Management studio see below image
- The scalar function is a function that may or may not have parameters but always return a single value. The return type of scalar function can be any data-type except text, image, cursor, and timestamp. The scalar function may or may not have parameters but it should return a single value.
Why this function called scalar function because it returns a single value.
In-built function with parameter
select ABS(-1000) // op 1000
In-built function with 0 parameter
Select GetDate() //
General Syntax for Scalar function:
CREATE FUNCTION [schema_name.]function_name (parameter_list) RETURNS return-data-type AS BEGIN statements RETURN value END
How to create User-defined Scalar function in the SQL server?
Create function GetAge(@bdate nvarchar(100)) returns int as begin declare @age Int set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate()); return @age; end
In the above function, we calculate the age.
To call this function if you only use
Select function-name(parameter); then it will give an error to overcome this error we need to give a fully qualified name or 2 part name or at-least database owner and name append before the function name. In our case, dbo is a database owner.
select dbo.GetAge('12/01/1988'); // op- 31
Second example: you can use this in select clause as shown below
select id,first_name,last_name,dbo.getage(dateofbirth) from studentTable
You can do this in store-procedure also but you can not use stored procedure in select clause and where clause.
How to alter function in the SQL server?
ALTER function [dbo].[GetAge](@bdate nvarchar(100)) returns int as begin declare @age Int set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate()); return @age; end
The alter keyword needs to use instead of create statement and whole function you need to write here.
How to delete or remove a function from the SQL server?
You need to use a drop keyword and function name to remove function in the SQL server.
Also, a fully qualified name or database administrator name needs to use along with function name if you use only function name then it will give an error.
Drop function function-name;
drop function dbo.getage
Inline Table-valued function
What is the table-valued function?
The table-valued function is a user-defined function that returns the table as a data type. The return type is a table and it is treated as a table.
In this type of function, we return a table and in previous we learn in that we return a single value.
General Syntax of table-valued function:
create function function_name(@parameter datatype) returns table as return (select * from table_name) end
- In the above general syntax, you will see returns return type is table means this function will return table.
- In this function, the body is not enclosed in Begin and End block
- Whatever table is returned that table structure specifically depends upon what select statement you used.
- The table-valued function is treated as a table.
If you use, begin and end statements in this function create syntax you will get below error.
State 31, Procedure getEmployeeByGender, Line 7 [Batch Start Line 0]
–Incorrect syntax near ‘BEGIN’.
create function getEmployeeByGender(@gender nvarchar(10)) returns table as return (select * from Employee where Gender=@gender);
In the above function, we pass gender as a parameter and this parameter is used in where clause in the below select statement.
To call this function we need to use below syntax.
Select * from function_name(parameter);
select * from getEmployeeByGender('male')
In inline-table-valued function when you call this function you can use where clause in that.
select * from getEmployeeByGender('male') where DepartmentId is null
Where we can use Inline Table-Valued functions?
- Inline table-valued functions can be used to achieve the functionality of parametrized views
- You can use a table which is returned by inline table valued function for joins or to form join with another table.
Query By Using Table-valued function with another table
select * from getEmployeeByGender('male') as e join Department D on D.DepartmentID=E.DepartmentID;
How to modify the table-valued function?
You can use alter keyword instead of create a word in function creation. The rest of the script is the same.
alter function [dbo].[getEmployeeByGender](@gender nvarchar(10)) returns table as --begin return (select EmpID,full_name,DepartmentID,Salary from Employee where Gender=@gender); --end GO
Multi-statement table-valued functions (MSTVF)
A multi-statement table-valued function is a table-valued function that returns the result of multiple statements.
It is useful because you can execute multiple queries inside that function.
In this type of function, you need to define a table variable and return that variable.
Also, you can create the structure of a table.
create function fn_MSTVF() returns @tbl Table (names varchar(100),empid int, gender varchar(10),salary float(2) , departmentid int) as begin insert into @tbl select full_name,empid,gender,salary,departmentid from Employee return end; select * from fn_MSTVF() where gender='male'
What is the difference between the table-valued function and multi-statement table-valued function?
- In the Inline-table-valued function, we don’t have the structure of a table in return statement whereas in a multi-statement table-valued function we have the structure of a table in returns clause.
- The inline table-valued function doesn’t have to begin and end block while MSTVF has begin and end block.
- An inline-table-valued function has better performance than MSTVF when you able to achieve function using inline-table-valued try to used over MSTVF.
- It is possible to update a table using inline-table-valued but can’t be using MSTVF
update fn_MSTVF() set departmentid=1 where empid=1 // gives error can’t modify update getEmployeeByGender('male') set departmentid=null where empid=1 //update successfully underlying table.
Functions Important Concept In SQL Server 2020
The function is either deterministic or non-deterministic at any given time in SQL.
Deterministic and Nondeterministic function:
It is a function that returns the same value or results at any time no matter how many times you run the same function with specific input and given the state of the database remains the same.
Ex. Square(), Avg(), Sum()
Note: all aggregate functions are deterministic functions.
These are the function which may return different results each time they called with a specific set of input values even if the database state that they access remains the same.
This function act as deterministic as well as non-deterministic based on the seed value.
- If you don’t provide any seed value to Rand function it will give you a different result every time until you call that function.
- When you provide seed value in case of rand function it will take an integer as a seed value and if we pass 1 every time it will give you the same result.
Encryption of function definition using with Encryption Option:
As we are able to encrypt the stored procedure similar way we can encrypt the function also in the sql server.
See below image we create a function first and after that, we call that function to see is this working fine or not.
After this, we call this function with the help of an in-built stored procedure which is sp_helptext and this procedure is used for getting the function text.
--drop function dbo.fn_getEmployeeEmailByID; Create function fn_getEmployeeEmailByID(@id int) returns nvarchar(200) as begin return (select email from Employee where EmpID=@id) end; select dbo.fn_getEmployeeEmailByID(10); sp_helptext fn_getEmployeeEmailByID;
To avoid this and we have scenario we don’t want the end user to see our code then we need to encrypt this function and by using Encryption keyword we can do that see below code.
As we already create function we will alter and add encryption tag in that
Alter function fn_getEmployeeEmailByID(@id int) returns nvarchar(200) with encryption as begin return (select email from Employee where EmpID=@id) end;
After doing this when you run below command you will get an error.
Below is the error we got.
The text for object ‘fn_getEmployeeEmailByID’ is encrypted.
Schema binding specifies that the function is bound to the database objects that it references. When you specify schema binding, the base object can not be modified or deleted in any way that affects our function definition.
To achive modification you need to modify your function first then you will able to modify a base object.
create function getnameByID(@id int) returns nvarchar(10) as begin return (Select name from A where id=@id) end;
See above function create a statement in this we use ‘A’ table which, suppose accidentally anyone drops that table or modify column names or delete columns on which our function depends then our function will not execute properly and it will throw an error.
So to avoid this we use Schemabinding Option.
Now our function depends on the table ‘A’. I will drop that table A.
drop table A; select dbo.getnameByID(1);
After drop table and run our function we get below error.
Msg 208, Level 16, State 1, Line 10 Invalid object name ‘A’.
To avoid this we need schema binding, for that, we need to use this with SchemaBinding
So, if I want schema binding we need to alter the function and execute that function the syntax for this is below.
alter function getnameByID(@id int) returns nvarchar(10) with SchemaBinding as begin return (Select name from A where id=@id) end;
By doing this we get an error which is below
Msg 4512, Level 16, State 3, Procedure getnameByID, Line 5 [Batch Start Line 1]
Cannot schema bind function ‘getnameByID’ because the name ‘A’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
To overcome this error we need to use table name in 2 parts like below dbo.A
alter function getnameByID(@id int) returns nvarchar(10) with SchemaBinding as begin return (Select name from dbo.A where id=@id) end;
After this, our function modified successfully and if we try to delete the table we get below error.
Msg 3729, Level 16, State 1, Line 9
Cannot DROP TABLE ‘A’ because it is being referenced by object ‘getnameByID’.