Creating and Using Inline Table-Valued Functions

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Excellent article, very thorough. Thanks for sharing this.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • sqlfriend

    SSC Guru

    Points: 52469

    Thanks for the great article!  Very through and easy understand!

    Can someone change my rate to 5 stars instead of one, not sure how to use the rate star, when I clicked the first star without having time to click next, it jumped to the end, so it ended one star.

  • kimani_m

    SSC Enthusiast

    Points: 149

    Thank you <a title="Go to ChrisM@Works.

  • kimani_m

    SSC Enthusiast

    Points: 149

    Glad you found it easy to understand sqlfriends. Thank you.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

    Thanks for this nice write-up Kimani, good job!
    😎

  • kimani_m

    SSC Enthusiast

    Points: 149

    Thanks Eirikur. Hope you found it useful.

  • Jeff Moden

    SSC Guru

    Points: 997349

    Agreed with the others.  Nice job on the article, Kimani .

    BTW/FYI... you CAN actually call a stored procedure from within a function using OPENROWSET.  Because of OPENROWSET (it won't take variables) and the inability to use dynamic SQL in a function (although I'll be someone even figured a way past that), it has some fairly limited uses but for those uses it works a treat.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • kimani_m

    SSC Enthusiast

    Points: 149

    Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
    I ran the following script on my Dev machine and it certainly worked a treat.

    USE StackOverflow2010;
    GO
    sp_configure 'show advanced options',1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE
    GO
    CREATE OR ALTER PROCEDURE dbo.NoddyProc
    AS
    BEGIN
      SELECT 1 AS Num;
    END
    GO
    CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
    RETURNS TABLE
    AS RETURN (
    SELECT np.Num
    FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
          'EXECUTE dbo.NoddyProc') AS np
    );
    GO
    SELECT Num
    FROM dbo.itvfnNoddyFunction();
    GO

  • Jeff Moden

    SSC Guru

    Points: 997349

    kimani_m - Wednesday, January 23, 2019 7:36 AM

    Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
    I ran the following script on my Dev machine and it certainly worked a treat.

    USE StackOverflow2010;
    GO
    sp_configure 'show advanced options',1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE
    GO
    CREATE OR ALTER PROCEDURE dbo.NoddyProc
    AS
    BEGIN
      SELECT 1 AS Num;
    END
    GO
    CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
    RETURNS TABLE
    AS RETURN (
    SELECT np.Num
    FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
          'EXECUTE dbo.NoddyProc') AS np
    );
    GO
    SELECT Num
    FROM dbo.itvfnNoddyFunction();
    GO

    Well Done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David McKinney

    SSChampion

    Points: 10449

    Is the definition of schemabinding not backwards?  i.e. Surely it prevents changes to the underlying objects and not the function?

  • kimani_m

    SSC Enthusiast

    Points: 149

    Hi David. Thanks for pointing that out and as you mentioned, schemabinding will prevent changes to the underlying objects, rather than the function. A better BOL definition can be found here (under schemabinding), rather than the previously referenced one.

    I've attached a script(itvf_schemabinding) in case someone would like to observe that behaviour.

    Cheers. Kimani

    • This reply was modified 4 months, 1 week ago by  kimani_m.
  • kimani_m

    SSC Enthusiast

    Points: 149

    DROP TABLE IF EXISTS dbo.please_remove;
    GO

    CREATE TABLE dbo.please_remove (
    first_col int NOT NULL
    );
    GO
    INSERT INTO dbo.please_remove
    (first_col)
    VALUES
    (1);
    GO

    CREATE OR ALTER FUNCTION [dbo].[itv_fn_please_remove] (@id int)
    RETURNS TABLE
    WITH SCHEMABINDING AS
    RETURN (
    SELECT @id AS id
    FROM dbo.please_remove
    );
    GO

    --1. Function returns expected result e.g. 1
    SELECT Id
    FROM [dbo].[itv_fn_please_remove] (1);
    GO

    --2. Dropping underlying objects fails due to schema binding reference
    DROP TABLE dbo.please_remove;
    GO

    --3. Dropping underlying objects succeeded only after dropping binding i.e. dropping the function first

    --DROP FUNCTION dbo.itv_fn_please_remove;
    --GO
    --DROP TABLE dbo.please_remove;
    --GO
  • kimani_m

    SSC Enthusiast

    Points: 149

    Comments posted to this topic are about the item Creating and Using Inline Table-Valued Functions

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply