What is User Defined Functions (UDF) in SQL Server?
What are the types of User Defined Function in SQL Server?
How to create User Defined Function in SQL Server?
How to ALTER and DROP user defined functions in SQL Server?
What are the advantages of User Defined Functions in SQL Server?
As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.
Sub Program blocks are of two types in SQL Server.
1. Procedures
2. Functions.
In this article I will be focusing on Functions.
There are two types of Functions in SQL Server
System Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
What are the types of User Defined Function in SQL Server?
Functions are of 3 types in SQL Server.
Scalar Functions
Inline Table Valued Functions
Multi-Statement Table Valued Functions.
How to create User Defined Function in SQL Server?
Let’s understand each type of User Defined Functions with one simple example.
Scalar Functions
A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.
Below is the syntax to create Scalar Type User Defined Functions
Syntax
CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS Data-Type
WITH Function-Options
AS
BEGIN
Function-Body
RETURN Scalar-Expression
END
Function-Name– This is the function name, you have to remember this name.
@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.
Function-Options – Functions options can be any of these two
AS
RETURN (SELECT STATEMENT)
Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.
SQL Code –
CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)
RETURNS TABLE
AS
RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)
Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder àTable-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)
Calling the MYINLINETABLEVALUEDFUNCTION function:
SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)
As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.
Multi-Statement Table Valued Functions
Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.
Below is the syntax to create Multistatement Table Valued Type User Defined Functions
Syntax
CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS @Return_Variable TABLE <Table-Type-Definition>
WITH Function-Options
AS
BEGIN
Function-Body
RETURN
END
Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.
SQL Code –
CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )
RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)
AS
BEGIN
DECLARE @SALARY MONEY,@ANNUALSALARY MONEY
SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)
SET @ANNUALSALARY= @SALARY*12
INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)
RETURN
END
Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)
Calling the MYMSTABLEVALUEDFUNCTION function:
SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)
As you can see from the above result set our recently created function is returning a table variable with two columns.
How to ALTER and DROP user defined functions in SQL Server?
ALTER Function
Once you have created your functions in SQL Server, you might want to make some changes into it.
You can ALTER User Defined Functions using ALTER Statement. At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.
Drop Function
Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.
Syntax
DROP FUNCTION FUNCTION-NAME
Example
DROP FUNCTION MYSCALARFUNCTION
What are the benefits of User-Defined Functions?
The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.
Function
Definition: a bunch of SQL Statements which can be stored under 1 name.
Type of Function
· Scalar Function
o Returns a single value
CREATE FUNCTION Fun1 (@a int)
RETURNS int
AS
BEGIN
DECLARE @B int
SET @B = @A
RETURN(@b)
END
· Table valued Function
o Returns a table (only 1)
CREATE FUNCTION Fun2 (@a int)
RETURNS @temp table (B int)
AS
BEGIN
INSERT INTO @temp (B) VALUES (@A)
RETURN
END
· Aggregate Function
· System Function
o Mathematical functions, Date functions, etc…
Difference between SP and Function
A scalar valued function can be used in SELECT and WHERE clause and a table-valued function can be used in FROM clause. SP cannot be used anywhere
Function won’t except OUTPUT pram, SP does
You cannot call an SP, cannot create #TEMP table, cannot use DML and DDL statements inside function. SP will allow.
Function can be used as User Defined DataType, SP cannot
You cannot return text, image, timestamp from a function
Default Param
Function in WHERE clause
When we are using the function in the SELECT statement it returns data depends on the number of records retrieve by the select statement and what type of value is passed in the scalar function. The performance of the MS SQL Query is little bit degraded using the scalar function. But developer often used it for encapsulate frequently performed logic.
But if we saw some SELECT statement, we can see that the functions can be used in WHERE conditions also. Using scalar or User defines function in WHERE clause is a good idea? Is this hampering the performance?
This article is related to it.
Is it Bad?
Using function on WHERE clause causes Index scan. The reason for this is that the function value has to be evaluated for each row of data to determine it matches our criteria.
How we understand it
To understand it properly, here I am taking an example. We compare the Actual Execution plan of the Query and understand how the function in the WHERE clause effects the query by Index Scanning.
Step-1 [ Creating the Base Table ]
-- Base Table
IF OBJECT_ID('tbl_EMPDTLS') IS NOT NULL
BEGIN
DROP TABLE tbl_EMPDTLS;
END
GO
CREATE TABLE tbl_EMPDTLS
(EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPFNAME VARCHAR(50) NOT NULL,
EMPLNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
EMPEMAIL VARCHAR(50) NOT NULL,
DOJ DATETIME NOT NULL);
GO
Step-2 [ Creating the Index Information ]
-- Creating Non clustered Index
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPFNAME')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPFNAME;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPFNAME
ON tbl_EMPDTLS (EMPFNAME)
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPGRADE')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPGRADE;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPGRADE
ON tbl_EMPDTLS (EMPLNAME);
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPEMAIL')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPEMAIL;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPEMAIL
ON tbl_EMPDTLS (EMPEMAIL);
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_DOJ')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_DOJ;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_DOJ
ON tbl_EMPDTLS (DOJ);
GO
Step-3 [ Inserting some records in the Table ]
-- Inserting Records
INSERT INTO tbl_EMPDTLS
(EMPFNAME, EMPLNAME, EMPGRADE, EMPEMAIL, DOJ)
VALUES ('JOYDEEP', 'DAS', 'B', 'joydeep@abc.com','03-12-2006'),
('RAJECH', 'DAS', 'C', 'rajesh@abc.com', '01-12-2006'),
('SUKAMAL', 'JANA', 'B', 'suku@abc.com', '03-12-2004'),
('TUHIN', 'SHINAH', 'B', 'tuhin@abc.com', '07-12-2001'),
('SANGRAM', 'JIT', 'B', 'sangram@abc.com','01-10-2011'),
('SUDIP', 'DAS', 'A', 'sudip@abc.com', '07-11-1990'),
('RANI', 'LAL', 'B', 'rani@abc.com', '03-12-2006'),
('JOHN', 'IBRAHAM','C', 'john@abc.com', '01-05-2007'),
('BHUPEN', 'SINGH', 'A', 'bhapu@abc.com', '03-12-2006'),
('SAIKAT', 'SREE', 'B', 'saikat@abc.com', '01-12-1906'),
('SUJATA', 'LALA', 'B', 'sujata@abc.com', '03-12-2012'),
('RAJU', 'ROSTOGU','C', 'raju@abc.com', '03-12-2006'),
('ROHIT', 'KUMAR', 'C', 'rohit@abc.com', '01-10-2012'),
('VIPIN', 'PAUL', 'B', 'vipin@abc.com', '01-11-2006'),
('VINODH', 'CHOPRA', 'C', 'vinodh@abc.com', '03-12-2006'),
('KALLU', 'SHEK', 'B', 'joydeep@abc.com','01-11-2011')
GO
Step-4 [ Exciting the Query ]
-- Example Set-1
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME LIKE 'J%';
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE LEFT(EMPFNAME,1) = 'J';
-- Example Set-2
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME='JOYDEEP'
AND EMPLNAME='DAS'
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME+EMPLNAME='JOYDEEPDAS'
-- Example Set-3
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DOJ = '03-12-2004'
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DOJ < GETDATE()
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DATEDIFF(day, DOJ, '03-12-2004') = 0
GO
Conclusion
So from execution plan we find that using function in WHERE clause is a bad idea.
What are the types of User Defined Function in SQL Server?
How to create User Defined Function in SQL Server?
How to ALTER and DROP user defined functions in SQL Server?
What are the advantages of User Defined Functions in SQL Server?
As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.
Sub Program blocks are of two types in SQL Server.
1. Procedures
2. Functions.
In this article I will be focusing on Functions.
There are two types of Functions in SQL Server
System Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions.
What is User Defined Functions (UDF) in SQL Server?
A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions.
What is User Defined Functions (UDF) in SQL Server?
A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions.
What are the types of User Defined Function in SQL Server?
Functions are of 3 types in SQL Server.
Scalar Functions
Inline Table Valued Functions
Multi-Statement Table Valued Functions.
How to create User Defined Function in SQL Server?
Let’s understand each type of User Defined Functions with one simple example.
Scalar Functions
A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.
Below is the syntax to create Scalar Type User Defined Functions
Syntax
CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS Data-Type
WITH Function-Options
AS
BEGIN
Function-Body
RETURN Scalar-Expression
END
Function-Name– This is the function name, you have to remember this name.
@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.
Function-Options – Functions options can be any of these two
Encryption – Indicates that the Database Engine encrypts the catalog view columns that contains the text of the create function statement.
Schemabinding – Indicates that Functions is bound to the database object that it references. Object can’t be dropped until you drop the function or alter the Function without Schemabinding option.
Function-Body – This is the place where we write our logic.
Example
I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.
You can use below query to populate dummy data.
USE TEACHMESQLSERVER
GO
CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)
INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)
INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)
INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)
INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)
GO
SELECT * FROM EMP
Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.
SQL Code –
CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)
RETURNS MONEY
AS
BEGIN
DECLARE @TOTALSALARY MONEY
SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID
RETURN @TOTALSALARY
END
Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Scalar Function:
SELECT <owner>.<function-name> (Parameters values)
Calling the MYSCALARFUNCTION function:
SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY
As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.
Inline Table Valued Functions
Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.
Syntax
CREATE FUNCTION Function-Name
(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)
RETURNS TABLE
WITH Function-Options
Function-Body – This is the place where we write our logic.
Example
I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.
You can use below query to populate dummy data.
USE TEACHMESQLSERVER
GO
CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)
INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)
INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)
INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)
INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)
GO
SELECT * FROM EMP
Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.
SQL Code –
CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)
RETURNS MONEY
AS
BEGIN
DECLARE @TOTALSALARY MONEY
SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID
RETURN @TOTALSALARY
END
Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Scalar Function:
SELECT <owner>.<function-name> (Parameters values)
Calling the MYSCALARFUNCTION function:
SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY
As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.
Inline Table Valued Functions
Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.
Syntax
CREATE FUNCTION Function-Name
(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)
RETURNS TABLE
WITH Function-Options
AS
RETURN (SELECT STATEMENT)
Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.
SQL Code –
CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)
RETURNS TABLE
AS
RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)
Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder àTable-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)
Calling the MYINLINETABLEVALUEDFUNCTION function:
SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)
As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.
Multi-Statement Table Valued Functions
Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.
Below is the syntax to create Multistatement Table Valued Type User Defined Functions
Syntax
CREATE FUNCTION Function-Name
(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)
RETURNS @Return_Variable TABLE <Table-Type-Definition>
WITH Function-Options
AS
BEGIN
Function-Body
RETURN
END
Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.
SQL Code –
CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )
RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)
AS
BEGIN
DECLARE @SALARY MONEY,@ANNUALSALARY MONEY
SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)
SET @ANNUALSALARY= @SALARY*12
INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)
RETURN
END
Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.
Now our task is to call recently created a scalar function.
Syntax for calling a Table Valued Function:
SELECT *|<Column List> from <function-name> (Parameters values)
Calling the MYMSTABLEVALUEDFUNCTION function:
SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)
As you can see from the above result set our recently created function is returning a table variable with two columns.
How to ALTER and DROP user defined functions in SQL Server?
ALTER Function
Once you have created your functions in SQL Server, you might want to make some changes into it.
You can ALTER User Defined Functions using ALTER Statement. At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.
Drop Function
Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.
Syntax
DROP FUNCTION FUNCTION-NAME
Example
DROP FUNCTION MYSCALARFUNCTION
What are the benefits of User-Defined Functions?
The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.
Function
Definition: a bunch of SQL Statements which can be stored under 1 name.
Type of Function
· Scalar Function
o Returns a single value
CREATE FUNCTION Fun1 (@a int)
RETURNS int
AS
BEGIN
DECLARE @B int
SET @B = @A
RETURN(@b)
END
· Table valued Function
o Returns a table (only 1)
CREATE FUNCTION Fun2 (@a int)
RETURNS @temp table (B int)
AS
BEGIN
INSERT INTO @temp (B) VALUES (@A)
RETURN
END
· Aggregate Function
· System Function
o Mathematical functions, Date functions, etc…
Difference between SP and Function
A scalar valued function can be used in SELECT and WHERE clause and a table-valued function can be used in FROM clause. SP cannot be used anywhere
Function won’t except OUTPUT pram, SP does
You cannot call an SP, cannot create #TEMP table, cannot use DML and DDL statements inside function. SP will allow.
Function can be used as User Defined DataType, SP cannot
You cannot return text, image, timestamp from a function
Default Param
Function in WHERE clause
When we are using the function in the SELECT statement it returns data depends on the number of records retrieve by the select statement and what type of value is passed in the scalar function. The performance of the MS SQL Query is little bit degraded using the scalar function. But developer often used it for encapsulate frequently performed logic.
But if we saw some SELECT statement, we can see that the functions can be used in WHERE conditions also. Using scalar or User defines function in WHERE clause is a good idea? Is this hampering the performance?
This article is related to it.
Is it Bad?
Using function on WHERE clause causes Index scan. The reason for this is that the function value has to be evaluated for each row of data to determine it matches our criteria.
How we understand it
To understand it properly, here I am taking an example. We compare the Actual Execution plan of the Query and understand how the function in the WHERE clause effects the query by Index Scanning.
Step-1 [ Creating the Base Table ]
-- Base Table
IF OBJECT_ID('tbl_EMPDTLS') IS NOT NULL
BEGIN
DROP TABLE tbl_EMPDTLS;
END
GO
CREATE TABLE tbl_EMPDTLS
(EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPFNAME VARCHAR(50) NOT NULL,
EMPLNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
EMPEMAIL VARCHAR(50) NOT NULL,
DOJ DATETIME NOT NULL);
GO
Step-2 [ Creating the Index Information ]
-- Creating Non clustered Index
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPFNAME')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPFNAME;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPFNAME
ON tbl_EMPDTLS (EMPFNAME)
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPGRADE')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPGRADE;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPGRADE
ON tbl_EMPDTLS (EMPLNAME);
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_EMPEMAIL')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_EMPEMAIL;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_EMPEMAIL
ON tbl_EMPDTLS (EMPEMAIL);
GO
IF EXISTS(SELECT *
FROM sys.indexes
WHERE object_id=OBJECT_ID('tbl_EMPDTLS')
AND name ='IX_NONC_DOJ')
BEGIN
DROP INDEX tbl_EMPDTLS.IX_NONC_DOJ;
END
GO
CREATE NONCLUSTERED INDEX IX_NONC_DOJ
ON tbl_EMPDTLS (DOJ);
GO
Step-3 [ Inserting some records in the Table ]
-- Inserting Records
INSERT INTO tbl_EMPDTLS
(EMPFNAME, EMPLNAME, EMPGRADE, EMPEMAIL, DOJ)
VALUES ('JOYDEEP', 'DAS', 'B', 'joydeep@abc.com','03-12-2006'),
('RAJECH', 'DAS', 'C', 'rajesh@abc.com', '01-12-2006'),
('SUKAMAL', 'JANA', 'B', 'suku@abc.com', '03-12-2004'),
('TUHIN', 'SHINAH', 'B', 'tuhin@abc.com', '07-12-2001'),
('SANGRAM', 'JIT', 'B', 'sangram@abc.com','01-10-2011'),
('SUDIP', 'DAS', 'A', 'sudip@abc.com', '07-11-1990'),
('RANI', 'LAL', 'B', 'rani@abc.com', '03-12-2006'),
('JOHN', 'IBRAHAM','C', 'john@abc.com', '01-05-2007'),
('BHUPEN', 'SINGH', 'A', 'bhapu@abc.com', '03-12-2006'),
('SAIKAT', 'SREE', 'B', 'saikat@abc.com', '01-12-1906'),
('SUJATA', 'LALA', 'B', 'sujata@abc.com', '03-12-2012'),
('RAJU', 'ROSTOGU','C', 'raju@abc.com', '03-12-2006'),
('ROHIT', 'KUMAR', 'C', 'rohit@abc.com', '01-10-2012'),
('VIPIN', 'PAUL', 'B', 'vipin@abc.com', '01-11-2006'),
('VINODH', 'CHOPRA', 'C', 'vinodh@abc.com', '03-12-2006'),
('KALLU', 'SHEK', 'B', 'joydeep@abc.com','01-11-2011')
GO
Step-4 [ Exciting the Query ]
-- Example Set-1
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME LIKE 'J%';
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE LEFT(EMPFNAME,1) = 'J';
-- Example Set-2
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME='JOYDEEP'
AND EMPLNAME='DAS'
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_EMPFNAME))
WHERE EMPFNAME+EMPLNAME='JOYDEEPDAS'
-- Example Set-3
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DOJ = '03-12-2004'
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DOJ < GETDATE()
GO
SELECT *
FROM tbl_EMPDTLS WITH(INDEX(IX_NONC_DOJ))
WHERE DATEDIFF(day, DOJ, '03-12-2004') = 0
GO
Conclusion
So from execution plan we find that using function in WHERE clause is a bad idea.
