User Defined Function (UDF) became available in SQL Server 2000. User Defined Function (UDF) allow you to create routines that execute much like built-in functions, but they perform data manipulations specific to your business needs. For example, you could write a function that calculates the number of business days between two dates. Avoid their confusion with stored procedures and views. There are some thing common between them but all three are used in different scenarios and when used optimally none can take over other.
Query execution plan of User Defined Function (UDF) may be cached and perform well. So optimization and re use is provided.
As you will build and use the User Defined Function (UDF) you will have a good comprehension and will be able to compare these with SP and views, and will be able to choose the best that fit. So lets get in.
In T-SQL User Defined Function (UDF) fall under three categories.
- Inline table-valued
- Multi-statement table-valued
In coming lines we will go through brief description and use of these three categories.
Consider following piece of code to create a scalar UDF in pubs that will convert whole column of pubs database to upper case (for learning).
CREATE FUNCTION testScalar (@input varchar(50)) RETURNS varchar(50) as BEGIN DECLARE @output varchar(50) SET @output = UPPER(@input) RETURN @output END GO
Now to test the created function
SELECT dbo.testscalar(au_lname) FROM pubs..authors GO
INLINE TABLE-VALUED UDF:
Inline table-valued UDF returns a table data type. Result is based on single select statement that is used to define the result. You do not explicitly define the returned table, but use a single SELECT statement for defining the returned rows and columns.
The plus point is that unlike SP a User Defined Function (UDF) can be referenced in from clause of a query. It may be used to create joins to other tables. And I have to mention that unlike view they can accept parameters.
Consider the following piece of code to create an inline User Defined Function (UDF) that will return all rows from sales table with quantity greater than given parameter.
USE pubs GO CREATE FUNCTION testInLine (@input INT) RETURNS TABLE AS RETURN(SELECT * FROM SALES WHERE QTY > @input) GO
Now use the function to simply count the returned rows
SELECT COUNT(*) FROM testinline(2) GO
Like inline table-valued UDF a multi-statement table valued UDF also returns a result set and is referenced in from clause. But the difference is that these are not restricted to use simple select statement for data retrieval within function definition. But these can use multiple T-SQL statements to retrieve the required result.
Consider following piece of code to create and run a multi-statement UDF. The code is from sqlteam.com. I liked this brief example so its better to consider it here.
CREATE FUNCTION dbo.customersbycountry ( @Country VARCHAR(15) ) RETURNS @CustomersbyCountryTab TABLE ( [CustomerID] [NCHAR] (5), [CompanyName] [NVARCHAR] (40), [ContactName] [NVARCHAR] (30), [ContactTitle] [NVARCHAR] (30), [Address] [NVARCHAR] (60), [City] [NVARCHAR] (15), [PostalCode] [NVARCHAR] (10), [Country] [NVARCHAR] (15), [Phone] [NVARCHAR] (24), [Fax] [NVARCHAR] (24) ) AS BEGIN INSERT INTO @CustomersByCountryTab SELECT [CustomerID], [CompanyName],[ContactName],[ContactTitle], [Address],[City],[PostalCode],[Country], [Phone],[Fax] FROM [Northwind].[dbo].[Customers] WHERE country = @Country DECLARE @cnt INT SELECT @cnt = COUNT(*) FROM @customersbyCountryTab IF @cnt = 0 INSERT INTO @CustomersByCountryTab ( [CustomerID],[CompanyName],[ContactName], [ContactTitle],[Address],[City], [PostalCode],[Country],[Phone], [Fax] ) VALUES ('','No Companies Found','','','','','','','','') RETURN END GO SELECT * FROM dbo.customersbycountry('USA') SELECT * FROM dbo.customersbycountry('CANADA') SELECT * FROM dbo.customersbycountry('ADF')
like any T-SQL object you may alter or drop a UDF. And here is a use full code snippet to find all UDFs in any database.
SELECT * FROM [dbName]..sysobjects WHERE xtype = 'FN' GO
Just replace [dbname] with name of your database.
At the end here are some important attributes of UDF.
- User Defined Function (UDF) prohibit Usage of Non-Deterministic Built-in Functions, Functions GETDATE(), RAND() etc can not be used in User Defined Function (UDF).
- User Defined Function (UDF) Returns Only One Result Set or Output Parameter. Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
- User Defined Function (UDF) can not Call Stored Procedure. Only access to Extended Stored Procedure.
- User Defined Function (UDF) can not run dynamic SQL which are dynamically build in User Defined Function (UDF). Temporary Tables can not be used in UDF as well.
- User Defined Function (UDF) can not Return XML. FOR XML is not allowed in UDFUser Defined Function (UDF) does not support SET options. SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
- User Defined Function (UDF) does not Support Error Handling. RAISEERROR or @@ERROR are not allowed.
- You can use a User Defined Function (UDF) directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
- User Defined Function (UDF) cannot be used for DML operations (INSERT/UPDATE/DELETE.
- You can not use print statement inside a scalar User Defined Function (UDF)