> -------- Original Message -------- > Subject: Re: Views in SQL Server > From: "Mike yearwood" <[EMAIL PROTECTED]> > Date: Fri, July 28, 2006 11:59 am > To: [email protected] > > SQL Server 2000 and SQL Server version 7.0 incorporate a number of > changes to statement processing that extend many of the performance > benefits of stored procedures to all SQL statements. SQL Server 2000 > and SQL Server 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 SQL Server 7.0 retain execution plans for all SQL statements in > the procedure cache, not just stored procedure execution plans. The > database engine uses an efficient algorithm for comparing new > Transact-SQL statements with the Transact-SQL statements of existing > execution plans. If the database engine determines that a new > Transact-SQL statement matches the Transact-SQL statement of an > existing execution plan, it reuses the plan. This reduces the relative > performance benefit of precompiling stored procedures by extending > execution plan reuse to all SQL statements.
Your going to to get a benefit in a multiple of ways with SPs over adhoc queries. 1) Security. I can set your rights to NADA and let the SPs do all the work. 2) Ease of Reuse-ability. Write it once and reuse the sucker. 3) Testing. It's easy to know in a unit test what is happening and what isn't. 4) Maintenance of one piece of code vs knowing where your hard coded calls are. Po if performance is truly equal from your BOL, I still say in the bigger picture SPs are better and out perform AdHoc in total. _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
