Notes: 1. On Microsoft SQL Server 2000 a table-valued function which "wraps" a View may be much faster than the View itself. The following MyFunction is an example of a "function-wrapper" which runs faster than the underlying view MyView: CREATE FUNCTION MyFunction() RETURNS @Tbl TABLE ( StudentID VARCHAR(255), SAS_StudentInstancesID INT, Label VARCHAR(255), Value MONEY, CMN_PersonsID INT ) AS BEGIN INSERT @Tbl ( StudentID, SAS_StudentInstancesID, Label, Value, CMN_PersonsID ) SELECT StudentID, SAS_StudentInstancesID, Label, Value, CMN_PersonsID FROM MyView -- where MyView selects (with joins) the same columns from large table(s) RETURN END 2. On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the "function-wrapper". User-defined functions are subroutines made of one or more Transact-SQL statements that can be used to encapsulate code for reuse. It takes zero or more arguments and evaluates a return value. Has both control-flow and DML statements in its body similar to stored procedures. Does not allow changes to any Global Session State, like modifications to database or external resource, such as a file or network. Does not support output parameter. DEFAULT keyword must be specified to pass the default value of parameter. Errors in UDF cause UDF to abort which, in turn, aborts the statement that invoked the UDF. CREATE FUNCTION CubicVolume -- Input dimensions in centimeters ( @CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1) ) RETURNS decimal(12,3) AS BEGIN RETURN(@CubeLength * @CubeWidth * @CubeHeight) END Data type supported in Microsoft SQL Server 2000 Like a temporary table used to store results Mostly used to define temporary variable of type (table) and the return value of a UDF The scope is limited to function, stored procedure, or batch in which it is defined Assignment operation is not allowed between (Table) variables May be used in SELECT, INSERT, UPDATE, and DELETE CREATE FUNCTION to create UDF ALTER FUNCTION to change the characteristics of UDF DROP FUNCTION to remove UDF Apache Hive Apache Hive defines, in addition to the regular user-defined functions (UDF), also user-defined aggregate functions (UDAF) and table-generating functions (UDTF). Hive enables developers to create their own custom functions with Java. References External links Microsoft SQL Server reference for CREATE FUNCTION MySQL manual section on UDFs DB2 CREATE FUNCTION statement Databases SQL Subroutines