Title: Understanding UserDefined Functions in Microsoft SQL Server 2000 Vikrant Dalwale SQL Server Engineer
1Understanding User-Defined Functions in Microsoft
SQL Server 2000 Vikrant DalwaleSQL Server
EngineerProduct Support ServicesMicrosoft
Corporation
2What You Should Already Know
- TSQL query syntax for data modification (DML) and
data definition (DDL) - Basic concepts of stored procedures and views
3What You Will Learn
- Definition, types, and rules of user-defined
functions (UDF) - Difference between UDF and stored procedure
- When to use UDFs instead of stored procedures and
views - How to get information about existing UDFs in a
database
4Definition of UDF
- 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.
5Overview of UDF
- 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 a 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.
6Example
- CREATE FUNCTION CubicVolume
- -- Input dimensions in centimeters
- (_at_CubeLength decimal(4,1),
- _at_CubeWidth decimal(4,1) ,
- _at_CubeHeight decimal(4,1) )
- RETURNS decimal(12,3)
- AS
- BEGIN
- RETURN ( _at_CubeLength _at_CubeWidth
_at_CubeHeight ) - END
7Table Variable Overview
- 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
8Table Variables
- Table variables cannot be used in the following
cases - INSERT INTO (table variable) exec ltstored
procedure gt - SELECT INTO (table variable ) ltstatementsgt
9Defining User-Defined Function
- CREATE FUNCTION to create UDF
- ALTER FUNCTION to change the characteristics of
UDF - DROP FUNCTION to remove UDF
10Valid Statements in a UDF
- DECLARE to declare variables and cursors that
are local to function. - SET to assign values to a scalar and table
variables. - Cursor operations that reference local cursors.
Only FETCH statements that assign values to
local variables using INTO clause are allowed. - Control-of-flow statements (for example IF-ELSE,
WHILE, RETURN, GOTO, CONTINUE)
11Valid Statements in a UDF (2)
- SELECT statements
- UPDATE , INSERT and DELETE statements modify
table variables that are local to the function - EXECUTE statements that call an extended stored
procedure
12Rules for UDF
- The following rules apply when UDF calls an
extended stored procedure - The extended stored procedure cannot return
results to the client. - The extended stored procedure can connect back to
SQL Server however it should not be of the same
transaction as the function invoking the
extended stored procedure. - Functions that call an extended stored procedure
are marked non-deterministic. - The extended stored procedure will be executed in
the context of Windows Security account of the
SQL Sever service.
13Characteristics of Functions
- Schema-bound function
- Function created with WITH SCHEMABINDING clause
is schema-bound to database objects like tables,
views, and other UDFs that it references. - A function is schema-bound only if
- The UDFs and views referenced by the function are
also schema-bound. - The objects it references are all in the same
database as the function.
14Characteristics of Functions (2)
- Deterministic function
- A function is deterministic if it always return
the same value for the same input argument. - Example of deterministic function
- DATEADD() built-in function is deterministic
because it always returns the same result for a
set of given parameters.
15Conditions of a Deterministic Function
- A deterministic function should meet following
conditions - It does not access any table other than table
variable defined locally - It does not invoke any non-deterministic function
- It is schema bound
- It does not call any extended stored procedure.
16Characteristics Functions (3)
- Non-deterministic function
- A function is non-deterministic if it returns
different result each time it is executed. - Example of a non-deterministic function
- GETDATE() built-in function is non-deterministic
17Types of UDF
- Scalar function
- Functions are scalar valued if the RETURNS clause
specifies one of the scalar data types (other
then timestamp, text, ntext, image ) - Scalar valued function has to return a variable
of data type which is implicitly convertible to
the data type of the return value of the
function. - Use two-part name (owner.udf_name) or three-part
name (dbname.owner.udf_name) to invoke the UDF
18Types of UDF (2)
- Inline table-valued function
- The RETURN clause specifies TABLE with no column
list. Single SELECT statement makes up the body
of the function. - Example
- CREATE FUNCTION fn_NamesInRegion (_at_region
varchar(30)) - RETURNS TABLE
- AS
- RETURN (SELECT Names from customers where
Region_at_region)
19Types of UDF (3)
- Multi-statement table valued function.
- The RETURN clause specifies a TABLE type with
columns and their data types. - Example
- CREATE FUNCTION LargeOrderShippers (_at_amount
money) - RETURNS _at_OrderTable TABLE
- ( ShipperID int, Shippername varchar(100),Amount
money) - AS
- BEGIN
- INSERT _at_OrderTable
- SELECT ShipperID, CompanyName, cost
- FROM Shippers
- WHERE cost gt _at_amount
- RETURN
- END
20Rules When Calling UDF
- Arguments are bound to the function's parameter
by their position. - All arguments are needed.
- The DEFAULT keyword should be used to pass the
default value of the parameter. - Parameter binding by name is not supported for
function invocation as it is supported for
stored procedures.
21Use of Scalar-Valued UDF
- Computed columns example
- CREATE TABLE Employee (
- EmpId char (9) NOT NULL,
- Salary Decimal(10,2),
- Comm Decimal (10,2),
- BONUS as dbo.BONUS (Salary,Comm))
- SELECT EmpId, Salary
- FROM Employees
- WHERE BONUS gt 1000
22Use of Scalar-Valued UDF (2)
- SELECT clause example
- SELECT ord_num, dbo.MyDateFormat(ord_date,'/'
) - FROM Sales
- SELECT PROPER(name), address, city, state FROM
customers - Note
- SELECT Multiply (T.a, T.b) from T is slower
than - SELECT ab from T
23Use of Scalar-Valued UDF (3)
- WHERE/HAVING/GROUP BY/ORDER BY Clause
- SELECT EmpId, Fname, Lname from Employees
- WHERE dbo.BONUS(Salary,Comm) gt 1000
-
- Note Optimizer will do table scan.
- Source side of the SET lists in UPDATE statement
- UPDATE EMP
- SET BONUS fn_CalcBonus( SALARY, COMM )
- VALUES clause of an INSERT statement.
24Use of Scalar-Valued UDF (4)
- CHECK constraint.
- CREATE TABLE EmpSalary
- (
- EmpId Char (9) NOT NULL,
- Salary Money NOT NULL,
- CONSTRAINT Salary_Range
- CHECK (dbo.CheckRange ( EmpID, Salary ) 1)
- )
25Use of Scalar-Valued UDF (5)
- CASE expressions
- PRINT statements
- Function arguments
- RETURN statement
- Assignment and control-flow statements
26Use of Table-Valued UDF
- FROM clause of a SELECT/UPDATE/DELETE statement,
example - SELECT FROM tb_Employees AS E,
- dbo.fn_EmployeesInDept('shipping') as EID
- WHERE E.EmployeeIDEID.EmployeeID
- UPDATE titles SET ytd_sales t.ytd_sales s.qty
- FROM titles t, dbo.fn_Sales() s
- WHERE t.tile_id s.title_id
27Rewriting Stored procedure or View Logic as UDF
- CREATE VIEW vw_CustomerNamesINWA AS
- SELECT CustomerID,CompanyName FROM
Northwind.dbo.Customers - WHERE Region'WA'
- In-line UDF
- CREATE FUNCTION fn_CustomerNamesInRegion (_at_Region
nvarchar(30)) - RETURN table
- AS
- RETURN ( SELECT CustomerID,CompanyName FROM
- Northwind.dbo.Customers
- WHERE Region _at_Region )
- SELECT FROM fn_CustomerNamesInReion(N'WA')
- WHERE CustomerId 'vick'
28Rewriting Stored procedure or View Logic as UDF
(2)
- The stored procedure does not perform update
operations, except to table variables. - The stored procedure does not use dynamic EXECUTE
statements. - The stored procedure returns one result set.
- If the primary purpose of the stored procedure is
to build intermediate results used later in a
SELECT statement, for example - INSERT temp EXEC sp_getresults
- SELECT ... FROM temp , t1 WHERE....
- Could be written as,
- SELECT ... FROM fn_results(),t1 WHERE ....
29Rewriting Stored procedure or View Logic as UDF
(3)
- If a stored procedure operates only on a set of
scalar inputs, does not access any database
tables ( except table variables) and returns a
single output as an output parameter. Example - CREATE FUNCTION FactRec ( _at_n int )
- Returns int
- AS
- BEGIN
- if _at_n0 return 1
- return (_at_n dbo.factRec (_at_n-1))
- END
-
- SELECT dbo.factRect(10)
30Rewriting Stored procedure or View Logic as UDF
(4)
- Inline UDF to filter data returned by indexed
views. Example - CREATE FUNCTION fn_QuarterlySalesByStore
(_at_StoreID int) - RETURNS table
- AS
- RETURN ( SELECT FROM SalesDB.do.vw_Quarterly
Sales - WHERE StoredID _at_StoreID )
-
- SELECT FROM fn_QuarterlySalesByStored (14432
)
31Obtaining Information About Functions
- Sysobjects (Type "FN" - scalar, Type "TF" -
table valued UDF). - Syscomments contains text of CREATE FUNCTION
statement. - sp_help fn_name.
- sp_helptext fn_name.
32Obtaining Information About Functions (2)
- OBJECTPROPERTY (obj_id, 'property') function
33Obtaining Information About Functions (2)
- Three information schema views also report
information about UDF - ROUTINES
- PARAMATERS
- ROUTINE_COLUMNS
- Example
- SELECT FROM INFORMATION_SCHEMA.ROUTINE
- WHERE ROUTINE_NAME like myfun
34(No Transcript)