SQL Server Performance

What are the pros and cons in using SQL Server View comparing to using stored procedure?

Discussion in 'SQL Server 2005 General DBA Questions' started by pcsql, Aug 22, 2007.

  1. pcsql New Member

    If my objective is to return a resultset or multiple resultsets to a .net application for displaying information in a grid/multiple grids, should I use SQL Server View or stored procedure? This functionality will not be used continuously under normal situation.
    pcsql, Aug 22, 2007
    #2

  2. ndinakar Member

    Performance wise it should not matter. There are some additional things you can do in procs that you cant do in views. So depending on your business logic you can either write a proc or a view.
    ndinakar, Aug 22, 2007
    #3

  3. satya Moderator

    It depends on the version of SQL you are using, within 2005 both have advantages but within SQL Server 2000 and 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. There are also internal performance benefits to SQL Server for using stored procedures vs. ad-hoc SQL script. When stored procedures are used SQL Server can cache or pre-compile the ‘execution plan’ that it uses to execute the SQL vs. having to recalculate the execution plan on each request.
    SQL Server 2000 and 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.
    satya, Aug 23, 2007
    #4

Share This Page