November 16, 2015 at 10:46 pm
Comments posted to this topic are about the item Making Deployments Simpler with Re-runnable Scripts
November 17, 2015 at 1:36 am
DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.
Instead, use this form:
IF NOT EXISTS ( <whatever> )
EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')
Go
ALTER PROCEDURE dbo.Fubar <real stuff goes here>
Go
This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.
November 17, 2015 at 1:37 am
Nice article Ed,
However, the first example with the Stored Procedure, I'd like it more the other way around, CREATE if it does not exist and then ALTER. That way you always preserve the permissions granted on the object. As soon as you DROP it, that information is lost and permissions must be recreated.
If you do granular permissions, that's a very important point.
Cheers!
November 17, 2015 at 1:38 am
Joe Garrick (11/17/2015)
DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.Instead, use this form:
IF NOT EXISTS ( <whatever> )
EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')
Go
ALTER PROCEDURE dbo.Fubar <real stuff goes here>
Go
This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.
Writing the same thing at the same time :-D:-D
November 17, 2015 at 1:51 am
sqldoubleg (11/17/2015)
Joe Garrick (11/17/2015)
DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.Instead, use this form:
IF NOT EXISTS ( <whatever> )
EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')
Go
ALTER PROCEDURE dbo.Fubar <real stuff goes here>
Go
This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.
Writing the same thing at the same time :-D:-D
Hi Guys, yes agree that this is a good approach!
November 17, 2015 at 5:30 am
Your example on checking the length of a column before altering it needs a "wrapper" to check that the column exists at all. If the column doesn't exist, then the SELECT won't return anything, and the ALTER TABLE statement will be attempted - and fail.
November 17, 2015 at 6:17 am
I personally like to also add a version table to the database, so that you can track which scripts that have been executed and by whom.
When you have many scripts, you can skip the ones that already have been executed.
If you make sure that you use the same name on the file as in the script you easily automate the databaseupgrade in an ant-script or executable to make sure all scripts have been executed.
Create a table for database versioning
/****************************************
Start Database versioning S0000.
****************************************/
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'DatabaseVersion' )
CREATE TABLE DatabaseVersion (
ScriptNo nvarchar(6) NOT NULL,
Description nvarchar(300) NOT NULL,
ExecutedByUser nvarchar(70) NOT NULL,
ExecutionTime datetime NOT NULL)
GO
INSERT INTO DatabaseVersion VALUES ('S0000', 'Start Database versioning', SUSER_NAME(), GETDATE())
GO
/****************************************
End Patch S0000.
****************************************/
And then use files named with a script number and use the same number in the end of each script and insert a record when the script has been executed.
INSERT INTO [DatabaseVersion] VALUES ('S0001', 'My first script', SUSER_NAME(), GETDATE())
and you know if the patch has been executed, when and by whom.
/Johan
November 17, 2015 at 8:24 am
I also use version numbering to control which script gets run next. To allow any script to run in any order would be pure insanity (a utopia that will never exist - combinatorial explosion). Some statements must run first in a batch such as Create Schema so I don't know how you could check it before creating it, maybe thru error recovery? My upgrades generally consist of 300 scripts or more and have minimal checking since a version number assures me which objects and dependencies exist.
Managing hundreds of databases at different versions becomes easy when I can do an "exec version" to see where they are currently at.
November 17, 2015 at 9:15 am
We'd use a not dissimilar approach for creating test data and removing sensitive data when copying from production to development. Each of the tables had a corresponding table in a schema called CHANGE. Each of those tables had an action column that contained either INSERT, UPDATE, DELETE.
This made is really simple in that you knew what tables were being changed, what actions were taken and when things failed. The script was then really easy to read and follow.
Come to think of it, I'll submit a request to write an article about this.
Andre
November 18, 2015 at 6:31 am
Ed Elliott (11/17/2015)
sqldoubleg (11/17/2015)
Joe Garrick (11/17/2015)
DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.Instead, use this form:
IF NOT EXISTS ( <whatever> )
EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')
Go
ALTER PROCEDURE dbo.Fubar <real stuff goes here>
Go
This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.
Writing the same thing at the same time :-D:-D
Hi Guys, yes agree that this is a good approach!
I've used this approach for years...many years.
What I do when creating a new object is to write the drop/create so I'm building it fresh every time. I also write any permissions necessary as a part of my release script. For data population, that's also in the release script. In a nutshell, I can run the whole release script at any time whether the objects already exist or not and everything will be created fresh. This means that anything I messed up or any existing data I created is gone.
Then, when it comes time to run it in production, everything is included, nothing gets missed and everything's new. Tables, procedures, functions, data and permissions are all included and everything in the proper sequence. Some releases get big enough that I isolate different things to their own files. Then I run each file in the proper sequence.
November 18, 2015 at 11:52 am
I also use a similar approach. I was surprised when working with a large ERP company that they did not use this approach and that they were surprised how well it worked. Their upgrades often took hours as they save the old table to a temp table, deleted, recreated then wrote the temp table back. Sometimes that's unavoidable but in their case it was pretty rare.
I don't do the versioning, that looks like a good idea.
November 23, 2015 at 2:11 pm
Thanks for the article.
April 20, 2018 at 7:59 am
Thank you for the post. I always like to see what others are doing to create repeatable DDL/DML scripts.
There are a few built in function you may want to consider:
Consider using this as an existence test if object_id(N'schemaName.NameOfObject'[, N'TypeCode']) is null ...rather than the more verbose... if not exists (select * from sys.objects where name = N'NameOfObject' and schema_id = schema_id('schemaName'))
It column order isn't important when you add a column to a table then you can test for column existence using if col_length('someTable','someColumn') is null.
One approach I use for merge statements is to use EXCEPT to eliminate duplicate/already updated values (provided the TargetTable isn't too big).merge into dbo.TargetTable as tgtYou could also create a CTE and use the CTE as the source for the MERGE. This has the advantage that you can use the CTE for a testing/validater to see what data will be returned before kicking it into a repeatable script.
using( select v.ColA, v.ColB, v.ColC
from(values ('A1', 20, 'C it first')
, ('A2', 21, 'C it next')
, ('A3', 22, 'C it last')
) v ( ColA, ColB, ColC)
except
select ColA, ColB, ColC
from dbo.TargetTable
) src on src.ColB = tgt.ColB
when matched then
update set ColA = src.ColA, ColC = src.ColC, UpdatedOn = getdate(), UpdatedBy = system_user
when not matched then
insert ( ColA, ColC, CreatedOn, CreatedBy )
values ( src.ColA, src.ColC, getdate(), system_user)
output $action [Action], Inserted.*;
--Paul Hunter
April 20, 2018 at 8:23 am
Ed Elliott - Tuesday, November 17, 2015 1:51 AMsqldoubleg (11/17/2015)
Joe Garrick (11/17/2015)
DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost. Instead, use this form: IF NOT EXISTS ( <whatever> ) EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1') Go ALTER PROCEDURE dbo.Fubar <real stuff goes here>Go This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.Writing the same thing at the same time :-D:-D
Hi Guys, yes agree that this is a good approach!
+1 for Create/Alter rather than Drop/Create. We have a client that uses replication, and Drop/Create was problematic because you can't drop sprocs that are being replicated.
As a result, we created Create/Alter versions for all server-side code, which I include below for everyone's benefit:
IF OBJECT_ID('[dbo].[<Name_Of_Function, , >]') IS NULL -- Check if Function Exists
BEGIN
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Function, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;') -- Create dummy/empty Function
EXEC('GRANT EXECUTE ON [<Name_Of_Function, , >] TO Public AS dbo;') -- Grant permissions to dummy/empty Function
END
GO
IF OBJECT_ID('[dbo].[<Name_Of_Sproc, , >]') IS NULL -- Check if SP Exists
BEGIN
EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Sproc, , >] AS SET NOCOUNT ON;') -- Create dummy/empty SP
EXEC('GRANT EXECUTE ON [<Name_Of_Sproc, , >] TO Public AS dbo;') -- Grant permissions to dummy/empty SP
END
GO
IF OBJECT_ID('[dbo].[<Name_Of_Function, , >]') IS NULL -- Check if Function Exists
EXEC('CREATE FUNCTION [dbo].[<Name_Of_Function, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;') -- Create dummy/empty Function
GO
IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if SP Exists
EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP
GO
IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists
EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1 AS Column_Name;') -- Create dummy/empty View
GO
April 21, 2018 at 12:09 am
hello there,
an addition to the "drop and create" question:in newer SQLServer versions finally the CREATE OR ALTER syntax was introduced. I'm not sure if in SQL 2014, but I know about it in SQL 2016.
Still I totally agree with Ed Wagner, having all the security stuff as part of the code. When dealing with topics like "continious delivery" this may be an important factor.
(I'm not having it right now, but have to deal with this soon).
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply