SQL Server Performance Forum – Threads Archive

Drop and re-create OR Modify

My developers used to pass me scripts to run against the server. Their scripts drop an object and then recreate them. For example, it drops a SP or a view and then recreates it. My question is that isn’t it better to just ‘modify’ the current SP or view? Regarding tables, I am curious because of constraints and relations.
CanadaDBA

You may find it more with SPs because ‘alter procedure’ did not exist with older version of SQL Server. Drop and recreate was the only option. Lot of developers from that age still continue this tradition in the new versions as well. But, It’s always better to modify than to drop and recreate.

Probably the reason is because its very easy to get a (re)creation script using enterprise manager, but a modify script generally has to be done by hand. The danger of dropping and recreating an sp is that the create might fail, after the drop has succeeded. But if the script comes from EM the chances of this are minimal I guess. From a performance point of view I dont think it makes a significant difference

From SQL 2K EM you can specify whether you want to include DROP & RECREATE steps before the object creation. 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.

We actually use the standard of Drop/Create for all of our rollout scripts. I disagree with ykchari on this one for once. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If the script fails, then we roll back to the previous version. A drop/create is the only way to modify the create date though to find out when an object was last updated. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>