User defined functions SQL Server

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

  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

>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

Advertisements

One thought on “User defined functions SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s