• 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

• 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

• 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

1. Only functions which performs in Deterministic manner can be used create indexed views or creating indexes on the computed columns.
2. No insert or update or delete statements except for table variables
3. All the statements which effects the state of the database are not allowed in the function body
4. You can not call stored procedures in the database only extended stored procedures
5. A function can have a maximum of 2,100 input parameters
6. 6.Only one output parameter in case of scalar UDF and single result set in case of In line or multi statement UDF’s.
7. UDF’s can accept and return any data types except TIMESTAMP data type.
8. No dynamic SQL, For xml is not allowed.
9. Does not support error handling Ex:@@ERROR_NUMBER(), No TRY..CATCH

Now lets dissect this

For more details

Please use the below video