In this post you will learn How to use
- What is a user defined function (UDF)
- Different types of UDF’s
- Properties of UDF’s
- How develop and execute UDF’s
- Advantages
- Limitations
- How to use Scalar UDF’s in computed columns
- What are Deterministic functions and Non Deterministic functions how do they effect UDF’s
- How do they effect performance of the UDF’s
- Creating indexes on the Computed columns
What is a UDF?
- UDF(user defined function) is a prepared code that accepts parameters and performs complex calculations and returns value can be scalar or a result set
- Depending on the what UDF returns UDF’s are categorized to two types
- Scalar Functions
- Table valued Functions
- Inline Table Valued Functions
- Multi statement Table Valued Functions
Benefits of UDF’s
- Once you create the function then you can, store it in the database, and call it any number of times in your code
- Faster execution (No re parsing, No reoptimization) by reusing the execution plans
- Ultimately reducing the network traffic
In case of UDF it is more important to know their limitations rather than their Benefits, the difference between UDF’s and Stored procedures is very less so you have to know the limitations which will help you to decide how to write your code
- Only functions which performs in Deterministic manner can be used create indexed views or creating indexes on the computed columns.
- No insert or update or delete statements except for table variables
- All the statements which effects the state of the database are not allowed in the function body
- You can not call stored procedures in the database only extended stored procedures
- A function can have a maximum of 2,100 input parameters
- 6.Only one output parameter in case of scalar UDF and single result set in case of In line or multi statement UDF’s.
- UDF’s can accept and return any data types except TIMESTAMP data type.
- No dynamic SQL, For xml is not allowed.
- Does not support error handling Ex:@@ERROR_NUMBER(), No TRY..CATCH
Now lets dissect this
For more details
>How to use Scalar Udf’s in computed columns
>What are Deterministic functions and Non Deterministic functions how do they effect UDF’s
>How do they effect performance of the UDF’s
>Creating indexes on the Computed columns
Please use the below video
One thought on “User defined functions SQL Server”