In SQL Server, a function is a precompiled and reusable piece of SQL statements that performs a specific task. In SQL Server, a function is stored as database object and can be executed when needed similar to stored procedure. Function is similar to stored procedure. A function must return a value but stored procedure may or may not return any value. Function may take input parameter and can return some value of same data type as defined in the function definition.
Based on the value returned by function, it is categorized into two main types in SQL Server: Scalar Functions and Table-Valued Functions (TVFs).
Types of functions
SQL Server functions are of two types.
- System defined functions
- User defined functions
Both of these can be of following types.
- Scalar functions (returns a single scalar value)
- Table valued function (return type is TABLE)
- Inline
- Multi-statement
Examples of some system defined scalar functions are UPPER, LOWER, LEN, LEFT, SUBSTRING, GETDATE etc.
You can get the list of all system functions in Programmability > Functions folder in all databases. Look into a user database.
Syntax of Function
The basic syntax of T-SQL function is as given below.
CREATE FUNCTION FunctionName([parameter-list-with-data type])
AS
BEGIN
Other statements
Return Statement
END
The T-SQL function has two parts - Header and Body. They are separated by AS keyword.
The header of function contains the CREATE FUNCTION statement. The function name is followed by parameters in parentheses. The parameters are optional.
The body of function begins after AS keyword and BEGIN-END block is part of body of the function.
Scalar function
Scalar function is a function which has return type of scalar data type. Scalar function returns a single scalar value.
Example of Scalar UDF
In the following example, we create a scalar function that takes two integer numbers and returns their sum. The function takes two input parameters of int data type and returns int data type value.
CREATE FUNCTION AddtwoNumbers
(
@number1 INT,
@number2 INT
)
RETURNS INT
AS
BEGIN
RETURN @number1 + @number2;
END
Execute the code to create UDF. The function is created as visible in the Object Explorer.
Run the following PRINT statement. We get 5 as result.
Print CAST(dbo.AddtwoNumbers(2, 3) AS VARCHAR)
REMARK: You must use the schema of UDF to execute a UDF.
Use of UDF in Real Life
UDF is a reusable piece of code. You can create a UDF and use it in various queries or stored procedures etc. For example, create a UDF to calculate age of person based on his DOB and use it in query to decide seniority of employees based on age.
create function GetSeniority
(
@dob as date
) returns varchar(10)
as
begin
declare @age as int
set @age = datediff(yy, @dob, getdate())
return
case
when @age < 18 then 'Minor'
when @age >= 18 and @age < 30 then 'Adult'
when @age >= 30 and @age < 50 then 'Matured'
when @age >= 50 and @age < 60 then 'Senior'
else 'Retired'
end
end
Go
CREATE TABLE Employees
(
Id int ,
first_name varchar(10) ,
last_name varchar(10) ,
Date_of_birth date
)
Go
INSERT INTO Employees VALUES
(1, 'Amit', 'Kumar', '11/11/2015')
,(2, 'Ajay', 'Raj', '2/25/1999')
,(3, 'Ritu', 'Sinha','8/17/1974')
Go
select Employees.*, dbo.GetSeniority(Date_of_birth) 'Seniority' from Employees
Inline Table Valued Function (ITVF)
Inline table valued function has following features.
- This function return type is TABLE data type.
- This function uses a single SELECT statement after RETURN keyword.
- BEGIN-END block must not be used in the body of this function.
Syntax
CREATE FUNCTION FunctionName()
RETURNS TABLE
AS
RETURN (SELECT Query);
Example
CREATE FUNCTION EmployeeInfo(@Empid int)
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees WHERE Id=@Empid)
;
Go
To execute inline table valued function, you write the parenthesis after function name and use it with a query.
select * from EmployeeInfo(1)
To drop the function, we use DROP FUNCTION statement.
DROP FUNCTION EmployeeInfo
Multi-Statement Table Valued Function (MSTVF)
Features:
Multi- statement table valued function has following features.
- This function return type is TABLE data type but the structure of the table is defined using table variable.
- The actual table data returned will be as per the definition of table structure.
- BEGIN-END block must be used with the body of this function.
- This function uses a multiple SQL statements in the body of function.
Look at the following example in which specific records of source table is inserted into target table and then that result set is returned by the MSTVF.
use AppliedkDb
go
if OBJECT_ID('Employees','U') is not null
drop table Employees
go
CREATE TABLE Employees
(
Id int ,
first_name varchar(10) ,
last_name varchar(10) ,
Date_of_birth date,
salary int,
gender varchar(1)
)
Go
INSERT INTO Employees VALUES
(1, 'Amit', 'Kumar', '11/11/2015', 20000, 'M')
,(2, 'Ajay', 'Raj', '2/25/1999', 28000, 'M')
,(3, 'Ritu', 'Sinha','8/17/1974', 25000, 'F')
Go
select * from Employees
go
Create function MSTVFDemo()
Returns @Data Table ( id int, first varchar(10), last varchar(10), sal int )
As
BEGIN
INSERT INTO @Data -- target table variable
select Id, first_name, last_name, salary -- source table columns list
from Employees
Return
END
select * from MSTVFDemo()
In case of MSTVF, we declare a table variable which will hold the result set returned by the MSTVF. A table variable is a variable which is of TABLE data type. We define the table structure for the table variable after the TABLE keyword in square brackets. Table structure means columns order and their data types etc.
Function body begins after AS keyword. We use BEGIN-END block with this function. The body contains multiple statements. For each variable declared in the table variable structure, there is a corresponding variable declared and initialized in the function body. Generally, we use SELECT statement to initialize the variables. When these variables are initialized individually then finally table variable is initialized with the help of these declared variables.
Encryption of Functions
We can encrypt a UDF using WITH Encryption clause just before AS keyword.
Example 1
CREATE FUNCTION AddtwoNumbers
(
@number1 INT,
@number2 INT
)
RETURNS INT
With Encryption
AS
BEGIN
RETURN @number1 + @number2;
END
Example 2
CREATE FUNCTION EmployeeInfo(@Empid int)
RETURNS TABLE
With Encryption
AS
RETURN (SELECT * FROM Employees WHERE Id=@Empid)
;
Go
Example 3
Create function MSTVFDemo()
Returns @Data Table ( id int, first varchar(10), last varchar(10), sal int )
With Encryption
As
BEGIN
INSERT INTO @Data -- target table variable
select Id, first_name, last_name, salary -- source table columns list
from Employees
Return
END
Schema Binding of Function
Schema Binding of Function means binding the function with objects upon which it is dependent. For example, UDF can return result set based on a table. Then that function will have binding with the table.
We can bind a UDF with schema object by using WITH SCHEMABINDING clause just before AS keyword. This clause is used to prevent dropping the database object used in the function
Example 1
CREATE FUNCTION EmployeeInfo(@Empid int)
RETURNS TABLE
With Schemabinding
AS
RETURN (SELECT Id, first_name, last_name FROM dbo.Employees WHERE Id=@Empid)
;
Go
In the above example, if you drop Employee table then error will be thrown.
Cannot DROP TABLE 'dbo.Employees' because it is being referenced by object 'EmployeeInfo'.
Remarks to Avoid Error
- We must use two-part format i.e. schema name with object name e.g. dbo.Employees in case of schema binding.
- SELECT clause must not use * for all columns.
Deterministic and non Deterministic functions
Deterministic function is the function that returns same value every time it is executed in query.
Non-Deterministic function is the function that does return same value every time it is executed in query. The examples are RAND, GETDATE, CURRENT_TIMESTAMP
No comments:
Post a Comment