SQL Server Performance

Difference between a View and a Stored Procedure

Discussion in 'General Developer Questions' started by blueFrench, Mar 9, 2005.

  1. blueFrench New Member

    Could anyone point me where I could learn the differences between a View and a Stored Procedure. Please spare me of the definition and what it basically do, I already know these things. What I want is the major or really "technical" difference of the two.

    Also, if I would use one in my application which is better?

    Thank you all.
  2. benwilson New Member

    At the risk of saying basically what a SP/view can do, I guess the main difference is that a Stored Procedure can alter your data, where as a view only returns it.

    Also, I believe from a performance point of view, a stored procedure is better as it caches the execution plan and will run faster as a result.

    Ben
  3. ranjitjain New Member

    ull get enough of information from BOL about views and SPs.
    and ofcourse SPs are much much better but views has its own value. sometimes when u have more table joining each other and u want the result in many different forms then the view will be better choice.

    as the name suggests view is to view result from different tables under one virtual table. where as SPs can be action or select query.
    u can select records from view but view can not have records resulting from an SP.

    Technically view is pre parsed and pre resulting feature meaning it generates output after designed and changes the result on its own as the table entries changes.

    the SP needs to executed to show result. it doesnt store result after design meaning its pre parsed query.

  4. satya Moderator

    Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures.

    http://www.vbforums.com/showpost.php?p=1760524&postcount=28 for related information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. mmarovic Active Member

    Views and stored procedures are different concepts that can be combined to offer consistent db API hiding implementation details. You can have just sp based API or you can cobine it with a view. View can be updatable if definition meets some conditions. For performance point of view use sp whenever you can, but if you really need (generic) queries you don't know in advance you would be forced to use view. The exception are indexed views that can provide significant performance improvement for selecting data and some performance degradation for data modifications.

Share This Page