posted by  on  

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.

Faster execution:

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:

Scalar Function:

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'.

Table-Valued Functions:

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.

System Functions:

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:

   (@Country nvarchar(15))
   RETURNS varchar(50)
   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'
return @Data

Lateshtclick Technology Blog
Tags SQL
posted by Latesht Click Latesht Click


Leave a Reply

Feel free to contribute!