There are three types of User-Defined functions in SQL Server:
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.
- Scalar Function
- Inline Function
- Multi-statement Table-valued Function
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.
No comments:
Post a Comment