Tuesday, July 13, 2010

Types of User Defined Functions (UDF) in SQL Server 2008?

There are three types of User-Defined functions in SQL Server:
  1. Scalar Function
  2. Inline Function
  3. Multi-statement Table-valued Function
Scalar UDFs

Scalar UDFs return a single value. They are similar to built-in functions such as GETDATE(), or OBJECT_NAME(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to do so, you must specify the underlying system-supplied data type instead.

In-line UDF
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.

Multi-statement UDFs

The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

3 comments:

  1. This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another good collection of php interview question and answer over internet.
    please check out this link...

    http://mindstick.com/Articles/9b3c9e65-6152-4933-acf8-15f8bbd3ff77/?Function%20in%20SQL

    Thanks

    ReplyDelete
  2. excellent piece of information, I had come to know about your website from my friend kishori, pune,i have read atleast 7 posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a lot once again, Regards, Single Row Function in sql

    ReplyDelete