As per the definition given by the various books and sites UDF are the database objects which user can create for computation or to get some specific result set on the basis of input parameters.
Just like stored procedure udf can also have the bunch of logical SQL statement to get some specific data but in udf you cannot use DDL or DML statements for physical database tables. Some of these operations can be performed for table variable.
Udf can be used in from class and join can be performed on those which give the feeling of view with parameter. I.e. for view parameters can’t be passed and but for udf it can be.
UDF’s are of three types
1. Scalar value udf:
Scalar value udf returns single value i.e. they performs the calculation or logic and returns the single value. These functions are generally used in select statement and execute for each row in select statement.
2. Inline table :
Inline table functions are the functions which returns result set. In these function the return clause contains the select statement with variable and the columns name are as per the column selected in select statement.
3. Multi statement table
Multi statement functions returns table variable. Return variable is declared as table variable and then the result set need to be inserted in that table and function returns the table with columns as declared in returned variable.
You can provide 1024 parameters in udf J
Generally developers use UDF in there select statement to get some specific result in query. So for each row the UDF is called in select statement and if the number of rows is very large then calling a db object in a sql query can result in performance issue of query.
Anyways functions provide the concepts of modular programming. Functions are very effective in many places.
No comments:
Post a Comment