At my first official DBA job, I was being trained the current DBA on deploying stored procedures. They had system where you received a ticket with the SQL file attached. You proceed to see if the procedure existed and if it did. You right click on it and look at who had access to it and print screened it. You then procedure to drop the procedure and the run the create procedure you were sent and then go through Enterprise Manger and re-grant the permissions. Being new to the company I waited a week or so before I asked why we did not use the ALTER command instead of all the other nonsense. This was a first exposure to having to deploy changes to the databases and still surprises considering they had a team of consultants sending them the changes to be made. They interestingly enough also had the bad practice of using Enterprise Manager later SSMS to insert columns in the table because they insisted that the created and modified user and date fields had to be last four columns. Luckily even after 10 years this database was only 20 GBs. Thankfully processes were change for future systems.
Fast forwarding to the future my current employer has every database engineer write the code where is completely redployable. So I could run it 100 times and it not break anything. I might look stupid but I wouldn’t cause any harm. We have processes in place that pull scripts from git and based on their name it automatically deploys it to the servers and databases it needs to in development environments. This is all a custom written solution. It automatically sends out emails when code checked in breaks the builds. To do hotrolls we have custom written solution that a database engineer wrote for us that uses a combination of Powershell and Nant to deploy the same files.
Overall I think the key to making changes to your databases is never do it with GUI, always write a script that will make the change and if ran a second wouldn’t do anything. This allows you to redeploy you code anywhere you need it. If changing a procedure always use ALTER, DROP/CREATES are just dumb. If adding a column, it can just go on the end of the table, you shouldn’t be relying on columns to be in a certain order you do have the ability to type your SELECT statement and place the columns in any order you want. Check to make sure your change doesn’t already exist. This has become easier with the 2016 introduction of CREATE OR ALTER syntax. But if you are on a lower version if you have to write a bit more code.
