Wednesday 16 December 2009

User-defined Functions in SQL Server

User-defined functions (UDFs) encapsulate logic for use in other queries. Views are limited to a single SELECT statement but user-defined functions can have multiple SELECT statements.

There are basically 3 categories of UDF:

  • Scalar-valued function
    • Returns a single, scalar value.
    • Can be used as column name in queries.
    • Can contain an unlimited number of statements as long as only one value is returned.
  • Inline function
    • Returns a variable of data type table.
    • Can only contain a single SELECT statement.
    • The structure of the returned value is generated from the columns that compose the SELECT statement.
    • A table variable need not be explicitly declared and defined.
  • Table-valued function
    • Can contain any number of statements that populate the table variable to be returned.
    • Useful when you need to return a set of rows.
    • A table variable must be explicitly declared and defined.
    • An advantages over a view is that the function can contain multiple SQL statements whereas a view is composed of only one statement.

Differences between Stored Procedures and UDFs:

  • UDF can be used in SQL statements whereas SPs can’t.
  • UDFs that return tables can be treated as another rowset and can be used in JOINs.
  • Inline UDF's can be thought of as views that take parameters and can be used in JOINs etc.