User Defined Functions

• What are User Defined Functions (UDFs)?

  User Defined Functions (UDFs) are routines that

  • can accept input parameters
  • perform an action
  • return result (single scalar value or table)

• Why use UDFs?

  Because

  • it can reduce execution time
  • it can reduce network traffix
  • allow for modular programming

• What is modular programming?

  • Software design technique
  • Separates functionality into independent, interchangeable modules
  • Allows code reuse
  • Improves code readability

• Scalar UDF with no input parameter

        -- Scalar function with no input parameters
        CREATE FUNCTION GetTomorrow()
            RETURNS date AS BEGIN
        RETURN (SELECT DATEADD(day,1,GETDATE()))
        END
  

• Scalar UDF with one parameter

        -- Scalar function with one parameter
        CREATE FUNCTION GetRideHrsOneDay(@DateParm date)
            RETURNS numeric AS BEGIN
        RETURN (
            SELECT
                SUM(
                    DATEDIFF(second, PickupDate, DropoffDate)
                )/360
                FROM
                    YellowTripData
                WHERE
                    CONVERT (date, PickupDate) = @DatePar
        )
        END;
  

• Scalar UDF with two input parameters

        -- Scalar function with two input parameters
        CREATE FUNCTION GetRideHrsDateRange (
            @StartDateParm datetime, @EndDateParm datetime
        ) RETURNS numeric AS BEGIN RETURN (
        SELECT
            SUM(
                DATEDIFF(second, PickupDate, DropOffDate)
            )/ 3600
        FROM YellowTripData
        WHERE
            PickupDate > @StartDateParm
            AND DropoffDate < @EndDateParm
        ) END;
  

---

Table Values UDFs

• Inline Table Valued Functions (ITVF)

        CREATE FUNCTION SumLocationStats(
            @StartDate AS datetime = '1/1/2017'
        ) RETURNS TABLE AS RETURN
        SELECT
            PULocationID AS PickupLocation,
            COUNT(ID) AS RideCount,
            SUM(TripDistance) AS TotalTripDIstance
        FROM YellowTripData
        WHERE CAST(PickupDate AS Date) = @StartDate
        GROUP BY PULocationID;
  

        CREATE FUNCTION CountTripAvgFareDay (
            @Month char(2),
            @Year char(4)
        ) RETURNS @TripCountAvgFare TABLE(
            DropOffDate date, TripCount int, AvgFare numeric
        ) AS BEGIN INSERT INTO @TripCountAvgFare
        SELECT
            CAST(DropOffDate as date),
            COUNT(ID),
            AVG(FareAmount) as AvgFareAmt
        FROM YellowTripData
        WHERE
            DATEPART(month, DropOffDate) = @Month
            AND DATEPART(year, DropOffDate) = @Year
        GROUP BY CAST(DropOffDate as date)
        RETURN END;
  

• Differences - ITVF vs. MSTVF

  | Inline | Multi Statement | | RETURN results of SELECT | DECLARE table variable to be returned | | Table column name in SELECT | BEGIN END block required | | No table variable | INSERT data into table variable | | No BEGIN END needed | RETURN last statement with BEGIN/END block | | No INSERT | | | Faster performance | |

---

UDFs in action

• Execute scalar with SELECT

        -- Select with no parameters
        SELECT dbo.GetTomorrow()
  

• Execute scalar with EXEC & stoer result

        -- EXEC & store result in variable
        DECLARE @TotalRideHrs AS numeric
        EXEC @TotalRideHrs = dbo.GetRideHrsOneDay @DateParm = '1/15/2017'
        SELECT
            'Total Ride Hours for 1/15/2017:',
        @TotalRideHrs
  

• SELECT parameter value $ sccalar UDF

        -- Declare parameter variable
        -- Set to oldest date in YellowTripData
        -- Pass to function with select
        DECLARE @DateParm as date =
        (SELECT TOP 1 CONVERT(date, PickupDate)
            FROM YellowTripData
            ORDER BY PickupDate DESC)
        SELECT @DateParm, dbo.GetRideHrsOneDay (@DateParm)
  

        DECLARE @CountTripAvgFareDay TABLE(
            DropOffDate date,
            TripCount int,
        AvgFare numeric)
        INSERT INTO @CountTripAvgFareDay
        SELECT TOP 10 *
        FROM dbo.CountTripAvgFareDay (01, 2017)
        ORDER BY DropOffDate ASC
  
        SELECT * FROM @CountTripAvgFareDay
  

  ---

  # Maintaining User Defined Functions

  • ALTER Function

          ALTER FUNCTION SumLocationStats (@EndDate as datetime = '1/01/2017')
          RETURNS TABLE AS RETURN
          SELECT
              PULocationID as PickupLocation,
              COUNT(ID) as RideCount,
              SUM(TripDistance) as TotalTripDistance
          FROM YellowTripData
          WHERE CAST(DropOffDate as Date) = @EndDate
          GROUP BY PULocationID;
    

• CREATE OR ALTER

        CREATE OR ALTER FUNCTION SumLocationStats (
        @EndDate AS datetime = '1/01/2017')
        RETURNS TABLE AS RETURN
        SELECT
            PULocationID as PickupLocation,
            COUNT(ID) AS RideCount,
            SUM(TripDistance) AS TotalTripDistance
        FROM YellowTripData
        WHERE CAST(DropOffDate AS Date) = @EndDate
        GROUP BY PULocationID;
  

        -- Delete function
        DROP FUNCTION dbo.CountTripAvgFareDay
  
        -- Create CountTripAvgFareDay as Inline TVF instead of MSTVF
            CREATE FUNCTION dbo.CountTripAvgFareDay(
                @Month char(2),
                @Year char(4)
            ) RETURNS TABLE AS RETURN (
            SELECT
                CAST(DropOffDate as date) as DropOffDate,
                COUNT(ID) as TripCount,
                AVG(FareAmount) as AvgFareAmt
            FROM YellowTripData
            WHERE
                DATEPART(month, DropOffDate) = @Month
                AND DATEPART(year, DropOffDate) = @Year
            GROUP BY CAST(DropOffDate as date));
  

• Determinism improves performance

  • A function is deterministic when it returns the same result given
    • the same input parameters
    • the same database state

• Schemabinding

  • Specifies the schema is bound to the database objects that it references
  • Prevents changes to the schema if schema bound objects are referencing it