SQL Server, Creating a View

  • Can a table-value function be included in a view?

    If so, how do I proceed? Have no idea of how to create it.

    Can you help me with it?

  • A table-valued function can be used just like a View. And yes, that includes being called from a View.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • how do I create one based on what I want to do?

    sorry, have no idea of where/how to do it or to start.

  • here is a start:

    CREATE FUNCTION dbo.fnDim_Concept()

    RETURNS

    @tblVar TABLE

    (col1 int,

    col2 int)

    AS

    BEGIN

    if (select failed from dimstatus) = 'N'

    INSERT Into @tblVar select * from dim_region_mgr

    else

    INSERT Into @tblVar select * from vw_dim_region_mgr

    RETURN

    END

    [/CODE]

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • wow Barry, thanks a million!!!! your help is highly appreciated. I will try it and get back to you. Now, how do I call a function from a view, a Select or what?

  • washingtonen (12/14/2008)


    wow Barry, thanks a million!!!! your help is highly appreciated. I will try it and get back to you. Now, how do I call a function from a view, a Select or what?

    I don't see a need to call the function from within a view. The function Barry provided or the view with the union all I suggested should both give you your desired result with roughly equal performance.

    But, if you want to do it, try something like:

    create view vdim_concept

    as

    select *

    from dbo.fnDim_Concept()

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • You guys are THE BEST

    THANKS!

  • And BTW, the Function worked like a CHAMP.

    If condition = N then the data is retrieved in 5 seconds else it take about 5 minutes, and that's exactly what I was expecting.

    Again, Thanks.

  • A table-valued function is used just like you would a table or a view:

    Select * from dbo.fnDim_Concept()

    You can use the function reference anywhere in a query that you would put a View or a table name.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Oops, I missed these replies before my answer above...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • washingtonen (12/14/2008)


    And BTW, the Function worked like a CHAMP.

    If condition = N then the data is retrieved in 5 seconds else it take about 5 minutes, and that's exactly what I was expecting.

    Again, Thanks.

    You should note that this it the exception, not the rule. And it probably only happens in this case because the optimizer is doing something wrong with your Union-based View. Normally a (multi-statement) function is slower than a View that returns the same results.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 11 posts - 16 through 26 (of 26 total)

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