May 5, 2017 at 7:25 am
I am trying to put together some standards documents for development on a new team, and I received some sage advice from wiser heads than mine that we should always DROP/CREATE objects, rather than ALTER. I have found this link: https://colinmackay.scot/2013/10/15/dropcreate-vs-alter-on-sql-server-stored-procedures/ blog suggesting that creating a stub and then ALTER works best since the script will work everytime, there is this post on SO (http://stackoverflow.com/questions/1644999/drop-create-vs-alter) that suggests Alter is preferable because of extended properties and permissions being retained, and finally we have a thread here on SSC (https://www.sqlservercentral.com/Forums/278456/Alter-Procedure-vs-DropCreate) where the opinion is that for migration purposes, DROP/CREATE is preferable.
Can someone help me understand why we would want to DROP/CREATE, rather than ALTER, beyond what is noted above?
Thanks
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 5, 2017 at 7:47 am
One thing that combines the best of both worlds is to test for existence, and create a stub object if it doesn't exist. After that, everything is an ALTER.
John
May 5, 2017 at 7:55 am
I hate to say it, but I don't know that I want to get tied down to doing it the same way every time regardless.
I mean, for stored procedures, yeah, ALTER is preferable because of the retention of security, etc. However, unless you also create a bunch of extra code to have the CREATE statements with stubs, that's going to fail. Maintaining that script header as part of every deployment is a ton of extra work and I'm lazy. Instead, I'd suggest using what works best situationally.
More importantly, I'd work with a tool that puts the code into source control. Most of those use CREATE for the base script because they will generate the CREATE or ALTER as needed from what's in source control.
Or, you could upgrade to SQL Server 2016 and get CREATE OR ALTER.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2017 at 8:06 am
For release scripts with new objects, I test for existence and drop, then create. For anything that already exists, I use alter. It allows me to run the release script as many times as necessary (and sometimes I need more than others :P) in development.
I know the creation of a stub will allow the same thing. It seems a little weird, but I can definitely see its merits. Like so many things in SQL, there's more than a couple ways to skin the same cat.
May 5, 2017 at 8:19 am
Grant Fritchey - Friday, May 5, 2017 7:55 AMI hate to say it, but I don't know that I want to get tied down to doing it the same way every time regardless.I mean, for stored procedures, yeah, ALTER is preferable because of the retention of security, etc. However, unless you also create a bunch of extra code to have the CREATE statements with stubs, that's going to fail. Maintaining that script header as part of every deployment is a ton of extra work and I'm lazy. Instead, I'd suggest using what works best situationally.
More importantly, I'd work with a tool that puts the code into source control. Most of those use CREATE for the base script because they will generate the CREATE or ALTER as needed from what's in source control.
Or, you could upgrade to SQL Server 2016 and get CREATE OR ALTER.
Wish I had that option, our DBAs aren't ready for that yet. Looks like CREATE OR ALTER doesn't apply to tables though?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 5, 2017 at 8:28 am
jonathan.crawford - Friday, May 5, 2017 8:19 AMGrant Fritchey - Friday, May 5, 2017 7:55 AMI hate to say it, but I don't know that I want to get tied down to doing it the same way every time regardless.I mean, for stored procedures, yeah, ALTER is preferable because of the retention of security, etc. However, unless you also create a bunch of extra code to have the CREATE statements with stubs, that's going to fail. Maintaining that script header as part of every deployment is a ton of extra work and I'm lazy. Instead, I'd suggest using what works best situationally.
More importantly, I'd work with a tool that puts the code into source control. Most of those use CREATE for the base script because they will generate the CREATE or ALTER as needed from what's in source control.
Or, you could upgrade to SQL Server 2016 and get CREATE OR ALTER.
Wish I had that option, our DBAs aren't ready for that yet. Looks like CREATE OR ALTER doesn't apply to tables though?
But you shouldn't DROP and CREATE tables. That will eventually cause data loss.
May 5, 2017 at 8:30 am
And don't forget about extended properties. Those would all be lost too if you DROP/CREATE. As would of course any original "create_date".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 5, 2017 at 8:50 am
jonathan.crawford - Friday, May 5, 2017 8:19 AMGrant Fritchey - Friday, May 5, 2017 7:55 AMI hate to say it, but I don't know that I want to get tied down to doing it the same way every time regardless.I mean, for stored procedures, yeah, ALTER is preferable because of the retention of security, etc. However, unless you also create a bunch of extra code to have the CREATE statements with stubs, that's going to fail. Maintaining that script header as part of every deployment is a ton of extra work and I'm lazy. Instead, I'd suggest using what works best situationally.
More importantly, I'd work with a tool that puts the code into source control. Most of those use CREATE for the base script because they will generate the CREATE or ALTER as needed from what's in source control.
Or, you could upgrade to SQL Server 2016 and get CREATE OR ALTER.
Wish I had that option, our DBAs aren't ready for that yet. Looks like CREATE OR ALTER doesn't apply to tables though?
No, not tables. Views, functions, procedures. However, it's pretty sweet. It sure does simplify all this mess.
I'd push hard on the source control thing. Your DBAs need to wake up & smell the 21st century. The water's fine. Come to the dark side, we have cookies. Etc. Plus, it acts as protection for your production systems, giving a good, safe, easy and fast way to undo or rollback changes since you know what was changed rather than having to rely on restores for that kind of thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2017 at 8:55 am
The following code in a separate file per table, means that the entire file is deployed each time there is a change. It is also re-runnable.
If there is a need to ALTER one of the fields (eg: INT to BIGINT, etc), this script is updated for all new deployments, and a separate ALTER script is created to modify existing fields. Fields are rarely altered after the initial deployment.
IF OBJECT_ID(N'Schema.TableName', N'U') IS NULL
BEGIN
CREATE TABLE Schema.TableName (
PK Fields ...
, CONSTRAINT PK_Name PRIMARY KEY CLUSTERED (FieldList)
);
END;
GO
IF COL_LENGTH(N'Schema.TableName', 'FieldName') IS NULL
BEGIN
ALTER TABLE Schema.TableName
ADD FieldName ...
-- CONSTRAINT DF_ConstName DEFAULT (DefValue);
END;
GO
May 5, 2017 at 9:12 am
Grant Fritchey - Friday, May 5, 2017 8:50 AMNo, not tables. Views, functions, procedures. However, it's pretty sweet. It sure does simplify all this mess.
I'd push hard on the source control thing. Your DBAs need to wake up & smell the 21st century. The water's fine. Come to the dark side, we have cookies. Etc. Plus, it acts as protection for your production systems, giving a good, safe, easy and fast way to undo or rollback changes since you know what was changed rather than having to rely on restores for that kind of thing.
To be fair, it's the vendor driving it really, we don't own our production software, we just build around it. But I'll let them know about the cookies. This time around, we're getting in on the TFS side of things, so moving in the right direction.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 5, 2017 at 10:05 am
jonathan.crawford - Friday, May 5, 2017 9:12 AMTo be fair, it's the vendor driving it really, we don't own our production software, we just build around it. But I'll let them know about the cookies. This time around, we're getting in on the TFS side of things, so moving in the right direction.
Cool beans. Not trying to be critical. I'm just used to resistance in this area so I push back regularly. It does sound like you're moving in the right direction. Plus, vendors are jerks (NOTE: I work for a vendor).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2017 at 10:18 am
In my workplace, we use DROP (after existence check) and then CREATE. Never ALTER except for tables and columns.
The reason being is we had a spate of no-longer-working-for-us "devs" who would push through ALTERs on procs that created problems months down the road. We couldn't tie the ALTERs to a specific release since SQL doesn't track that kind of modification automatically. So the standard was created that it must be a DROP-CREATE so that we could look at the CREATE date of a proc and know exactly when it got changed. Documentation (which is supposed to be in every function, view, and proc) isn't consistent or always done. The "created on" date of the object gives us the reference we need when six months to a year later that once-in-a-blue-moon problem crops up.
People actually got fired for the stuff that prompted this particular standard. One got fired the day after he pushed an ALTER PROC which was missing a WHERE clause, which also prompted peer-reviews of code before they go into test/qa. "DELETE FROM Table". WHOOPS.
May 5, 2017 at 12:49 pm
Brandie Tarvin - Friday, May 5, 2017 10:18 AMIn my workplace, we use DROP (after existence check) and then CREATE. Never ALTER except for tables and columns.The reason being is we had a spate of no-longer-working-for-us "devs" who would push through ALTERs on procs that created problems months down the road. We couldn't tie the ALTERs to a specific release since SQL doesn't track that kind of modification automatically. So the standard was created that it must be a DROP-CREATE so that we could look at the CREATE date of a proc and know exactly when it got changed. Documentation (which is supposed to be in every function, view, and proc) isn't consistent or always done. The "created on" date of the object gives us the reference we need when six months to a year later that once-in-a-blue-moon problem crops up.
People actually got fired for the stuff that prompted this particular standard. One got fired the day after he pushed an ALTER PROC which was missing a WHERE clause, which also prompted peer-reviews of code before they go into test/qa. "DELETE FROM Table". WHOOPS.
...aaand, now I'm paranoid. Thanks!
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
May 5, 2017 at 9:29 pm
John Mitchell-245523 - Friday, May 5, 2017 7:47 AMOne thing that combines the best of both worlds is to test for existence, and create a stub object if it doesn't exist. After that, everything is an ALTER.John
I agree with this basic approach especially since privs, extended properties, etc, etc, are frequently used and need to be preserved.
Prior to 2005, we always regenerated all privs, properties, etc, and then would do a conditional Drop and unconditional Create especially on code based objects. It was because there didn't used to be a Modified Date associated with objects and we used it to help keep track of what was promoted when.
Since 2005, we've leaned towards the idea of using the following method, which is a conditional stub similar to what John first mentioned on this thread.--===== If the object doesn't already exist, create a stub.
IF OBJECT_ID('<schemaname>.<objectname>') IS NULL
CREATE <object type> <schemaname>.<objectname>
<simple stub definition>
;
GO
--===== Now, whether it's a new or existing object, ALTER will always work.
ALTER <object type> <schemaname>.<objectname>
<final definition>
;
GO
This not only preserves any normally hidden additions, such as privs and extended properties, it also...
1. Preserves the original creation date.
2. Makes it so that the method doesn't have to change in our promotion script if a new object needed to be fixed, re-reved, and resubmitted to QA. Everything is always an ALTER. When you're promoting a 100 scripts for a major project, this can be a real time saver.
As a bit of a sidebar, we're religious about revision history in the header of all code objects. You can say what you want about version control tools, etc, etc, but having the revision history in the object header has been a huge lifesaver that has saved on much time and confusion. I'll also say that the auditors of all types absolutely love it because we also include the "ticket #" and description of the ticket. It also makes a search of the database for everything affected by one or more tickets a snap.
If you've ever had upper levels of management decide they wanted to change whatever version control you've had to something else, you'll find that revision history in the header is a lifesaver.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2017 at 3:28 pm
Jeff Moden - Friday, May 5, 2017 9:29 PMAs a bit of a sidebar, we're religious about revision history in the header of all code objects. You can say what you want about version control tools, etc, etc, but having the revision history in the object header has been a huge lifesaver that has saved on much time and confusion. I'll also say that the auditors of all types absolutely love it because we also include the "ticket #" and description of the ticket. It also makes a search of the database for everything affected by one or more tickets a snap.If you've ever had upper levels of management decide they wanted to change whatever version control you've had to something else, you'll find that revision history in the header is a lifesaver.
I couldn't agree more on that one. For every single DBA procedure I create, I use a comment block that gives the basic purpose, related procedures if it's one of a set, parameter explanations and version history. I didn't always do this, but when I found myself wondering what the procedure did and why I wrote it differently than normal, I've been pretty strict on myself since. It's a quick and simple thing that's saved me quite a bit of time.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply