SQL Server Performance

Tsql performance

Discussion in 'SQL Server 2005 General Developer Questions' started by SantoshKumar, May 20, 2009.

  1. SantoshKumar New Member

    Hi Friends,
    I want to know in which scenario the Table Valued Function is better than a Stored Procedure.
  2. madhuottapalam New Member

    One obevious reason is you can use Function in select , sp can not (exception using linked server)
    When you want to return multiple result set use SP
    SP has more flexibility interms of features like support xml/dynamic sql/DDL statement etc
  3. Sandy New Member

    Hi Santosh,
    As we aware of Function is used to return a value for the giving inputs and it basically comes to our mind when we think to perform some kind of calculation on numeric data. So the Microsoft comes up the new concepts Table Valued Function where you can combine both the selective data and complex calculation result in the same result set. I mean to say if you wants to perform some complex calculation which is dynamic every time then it is useful to use a function rather than a stored procedure. And also the same time you wants the result set along with the complex calculation then you can use a Table Valued Function rather than a Stored Procedure.
    Stored Procedure basically used to implements the application business logic in the backend side. I mean to say if you have more than a batch of query then it is best to use a stored procedure which will best for the application and all respects. In the time of need you can call the Table Valued Function in side stored procedure to get the desire outputs.
  4. SantoshKumar New Member

    Hi Sandy,
    [:cool:]It is extremely well. and NICE response from you. Thanks for the grate response from your side, but could you please explain me with one Example then I fell it is more clear cut to me on Table valid function and how can we use it under SP.

    Santosh kumar.A
  5. Sandy New Member

    Hi Santosh,
    Please find the SQL Script for the same.
    Hope you are happy now.:)
  6. SantoshKumar New Member

    Hi Sandy,
    Hurtfully Thanks for great and quick response on my Query,
    It is good example and it is worked out for me.
    but one request you on this topic,
    i.e. one more best example for Implementing table valid function (with multi parameters) in SP (where we can use generally in real time senario).
    I hope to get response from your end.
    Santosh kumar.A[:D]

  7. Sandy New Member

    Script for you......,
    Hope you are clear now. Try this it is very simple.
  8. SantoshKumar New Member

    Thanks a lot for your patience and quite a nice response on my query.
    Thank you very much.
    Santhosh kumar.A

  9. Sandy New Member

    [quote user="SantoshKumar"] Hi, Thanks a lot for your patience and quite a nice response on my query.
    Thank you very much.
    Santhosh kumar.A

    [:|]Santosh, I am also happy to see your patience too [:O]
  10. FrankKalis Moderator

    Maybe I haven't really understood what Sandy said, but I'm not sure I agree.
    First, there are 2 flavours of Table-Valued Functions:
    - Inline
    - Multistatement
    An inline TVF is basically nothing but a macro or a view that can take parameters. It will expand nicely into the execution plan of the whole statement from which the function is called. Inline TVF's are fine for resolving lookup data to its keys, or stuff like that. However, they are performance killers when used in a SELECT list as they are executed for every row of the resultset. Basically just like a cursor.
    A multistatement TVF is more like a stored procedure as it can contain complex logic in more than one step. However, I think the optimizer does not do as good as job on multistatement TVF's as it does on stored procedures. Meaning it is more likely to choose a suboptimal plan. Reason is that there are no statistics generated for TVF's and the optimizer still assumes that only one row is returned. If the function is to return only that one row, performance should be fine. However, if it returns more, things get worse and performance suffers.
    I have yet to find a good reason to use a multistatement TVF. I always go for a stored procedure. [:)]

Share This Page