Understanding UserDefined Functions in Microsoft SQL Server 2000 Vikrant Dalwale SQL Server Engineer

1
Understanding User-Defined Functions in Microsoft
SQL Server 2000 Vikrant DalwaleSQL Server
EngineerProduct Support ServicesMicrosoft
Corporation
2
What You Should Already Know

• TSQL query syntax for data modification (DML) and
  data definition (DDL)
• Basic concepts of stored procedures and views

3
What 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

4
Definition 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.

5
Overview 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.

6
Example

• 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

7
Table 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

8
Table Variables

• Table variables cannot be used in the following
  cases
• INSERT INTO (table variable) exec ltstored
  procedure gt
• SELECT INTO (table variable ) ltstatementsgt

9
Defining User-Defined Function

• CREATE FUNCTION to create UDF
• ALTER FUNCTION to change the characteristics of
  UDF
• DROP FUNCTION to remove UDF

10
Valid 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)

11
Valid 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

12
Rules 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.

13
Characteristics 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.

14
Characteristics 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.

15
Conditions 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.

16
Characteristics 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

17
Types 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

18
Types 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)

19
Types 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

20
Rules 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.

21
Use 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

22
Use 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

23
Use 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.

24
Use 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)
• )

25
Use of Scalar-Valued UDF (5)

• CASE expressions
• PRINT statements
• Function arguments
• RETURN statement
• Assignment and control-flow statements

26
Use 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

27
Rewriting 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'

28
Rewriting 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 ....

29
Rewriting 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)

30
Rewriting 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
  )

31
Obtaining 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.

32
Obtaining Information About Functions (2)

• OBJECTPROPERTY (obj_id, 'property') function

33
Obtaining 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)