SQL Server Performance Forum – Threads Archive

better option in modifying and deploying SP

Hi Folks,
Which is better option in modifying and deploying stored procedure in terms of performance.
1.Using Drop and Create
or
2.using alter command
As far as my knowledge,droping a stored procedure also drops the execution plan from the cache.

---

Using ALTER has the distinct benefit of preserving permissions on the SP, so they don’t have to be explicitly re-granted as they do with a DROP/CREATE. ALTER will generate a new plan too. Therefore I’d always go for ALTER.

---

In SQL 2000, the advantage of drop/create (not forgetting the permissions) is that the Date Created info is changed, which ALTER does not do. But I believe in SQL 2005 you now have Date Modified info?

---

Thats Correct. The last_altered column in INFORMATION_SCHEMA.ROUTINES is populated accurately in 2005. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Thanks folks I think I should go with Alter now

---

And also sys.objects table Modify_Date column MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Actually the change in sys.objects is just reflected in INFORMATION_SCHEMA.ROUTINES Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Generally we do DROP/CREATE. All our DROP/CREATE scripts have the permission script too at the bottom The devleopers are required to include the permission script. Since our application uses a single userID to connect its fairly simple..just one line of code. The advantage of dropping/recreating the proc is any query plans cached are cleared. So any results you see from the proc are most based on your recent code. There is no interference from previous query plans. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

---

See Brad’shttp://www.sql-server-performance.com/bm_using_plan_guides.asp article on using query plan guide in thsi case.
Salient points by Dinaker about allowing or granting Developers in this case, but in this case you can use IMPERSONATION to accomplish the task. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>