User Defined Functions is a useful tool in SQL Server. User Defined functions is used to perform a complex logic, it also used to accept parameters and return data.
Many times we have to write our complex logic which cannot be written using a single query. At that time to create a UDFs is best practice.
Why use them?
Allow modular programming:
We can create the function once, it is going to store in the database, so we can call it any number of times in our program. We can modify User-defined functions independently of the other program source code.
It is Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions.
The user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
Reduce network traffic:
An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression so it can be expressed as a function. The function can be invoked in the WHERE clause to reduce the number or rows sent to the client.
Types of Functions:
User-defined scalar functions return a single data value defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement.
For a multistatement scalar function, we have to define the function body in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value.
The return type can be any data type except – 'text, ntext, image, cursor, and timestamp'.
User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.
SQL Server provides many system functions that you can use to perform a variety of operations, but we cannot modify that functions.
How to create and use a Scalar User-Defined Function?
A Scalar user-defined function returns one of the scalar data types. You pass in 0 to many parameters and you get a return value. Below is an example:
CREATE FUNCTION (@Country nvarchar(15)) RETURNS varchar(50) AS BEGIN declare @Data varchar(30) select @Data = case @ Country when 'US' then 'New York' when 'Japan' then 'Tokyo' when 'India' then 'Delhi' when 'China' then 'Yunnan' else 'Unknown' end return @Data END