SQL Server Performance

view vs. SP

Discussion in 'General Developer Questions' started by tackett, Aug 9, 2004.

  1. tackett New Member

    Which is better to return a query set to a client, a view or SP? What are the advantages of using an SP over a view? I'm not planning on using views to do inserts/updates/deletes, just to return data.





    Thanks,
    Phil
  2. tdong New Member

    They are exactly what they say they are<br /><br />STORED PROCEDURE use for storing<br />VIEW is View <br /><br />if you just want to return data then use VIEW about performance they are pretty much the same ALL DEPEND of the traffic and the speed of your computer <img src='/community/emoticons/emotion-1.gif' alt=':)' /> since PERFORMANCE is a RELATIVE TERM<br /><br />This question was discuss here before but anyway here is an interesting link I post it again<br /<a target="_blank" href=http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx>http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx</a><br /><br /><br />May the best cheaters win
  3. tackett New Member

    Is there anything other than performance? I noticed that if you added a column to a table that column wouldn't show up in a view unless you re-compiled the view. Is there an advantage to having an SP at all when it comes to returning data?

    Views are easier for the client to work with because of their ability to allow customization. In a view the client can do order by's and stuff like that whereas in an SP I have to create all the scenerios ahead of time.


    If anybody has any real-world knowledge using views I would appreciate some feedback as well.


    Thanks,
    Tackett
  4. FrankKalis Moderator

    quote:Originally posted by tackett

    Is there anything other than performance? I noticed that if you added a column to a table that column wouldn't show up in a view unless you re-compiled the view. Is there an advantage to having an SP at all when it comes to returning data?
    You might want to read BOL on both concepts.

    quote:
    Views are easier for the client to work with because of their ability to allow customization. In a view the client can do order by's and stuff like that whereas in an SP I have to create all the scenerios ahead of time.
    Umh? Not true at all. Both objects can be used to return some information to the client. What then the client is doing with this information is not dependant whether it came from a view or an SP.



    --Frank
    http://www.insidesql.de
  5. Adriaan New Member

    The main point why we use SPs for reporting, not views, is that you can use parameters to filter the resultset in an ad-hoc manner. When using a view, the server has to process all the data covered by the view, and you can only do ad-hoc filtering after the entire resultset is returned to the client application. So if you need flexibility in filtering, use a stored procedure.

Share This Page