locked
Udfs RRS feed

  • Question

  • What is the major differences between Table Valued and (Inline or Multiline) udf in sql server 2005?

     

    Saturday, May 1, 2010 6:51 AM

Answers

  • Table-valued function (TVF) returns a table as opposed a scalar like INT, VARCHAR.

    You call scalar-valued function like: SELECT dbo.ufnCalculate 4,5

    You call table-valued function like: SELECT * FROM dbo.ufnCalculateMatrix 100

    You can JOIN and CROSS APPLY with TVF.

    Inline table-valued function (ITVF) is like a view with parameters. Multi-line TVF is like a stored procedure but no update to the database.

    Related example link: http://www.paladn.com/resources/links-and-tools/122.html

    CREATE FUNCTION datesales (@deadline as datetime)

    RETURNS TABLE
    AS
    RETURN ( SELECT *
    FROM sales WHERE ord_date > @deadline)
     

    USE PUBS
    GO
    select * from datesales('09/13/1994')


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    • Proposed as answer by Naomi N Sunday, May 2, 2010 4:47 AM
    • Marked as answer by KJian_ Friday, May 7, 2010 8:06 AM
    Saturday, May 1, 2010 7:14 AM