'create or alter' in programmable objects

Orm

Hi,

I'm in the process of evaluating ReadyRoll for our company.
One of the feature I'd want to use is 'create or alter' in programmable objects instead of 'drop + create'. I'm using version 14.4. I set target version of my project to SQL Server 2016 but programmable objects are still scripted as 'drop + create'.
So could you please explain when ReadyRoll will use 'create or alter' instead of 'drop + create'?

dnlnln

Thanks for your question and your interest in ReadyRoll.

To help answer your question, I'll start by providing a bit of context as to why ReadyRoll uses the DROP and CREATE statements in programmable objects: a given stored procedure may have one or more properties or sub-objects, such as permissions and extended properties. We use drop and create in order to reset such elements of state associated with the procedure with each deployment. Dropping the object with each deployment ensures that, for example, an extended property that was removed from the programmable object file is also cleaned up; if the file was to use ALTER or the newly-introduced CREATE OR ALTER, then drift would occur between the project and the target database due to the extended properties retained in the target. The difficulty in keeping track of such drift, as well as the risk of a future deployment failure as a result of the drift, is why we prefer to always recreate objects.

If you're comfortable with the risks associated with using CREATE OR ALTER, then you could make adjustments to the scripts yourself after the programmable objects are initially created to use this syntax instead. However please note that, if you intend to follow a connected workflow for making changes to the programmable objects (i.e. editing the objects in SSMS or via the SQL Server Object Explorer and using the ReadyRoll tool-window to import the changes), then you will need to re-apply the CREATE OR ALTERs to the changed files as unfortunately the scripting engine does not currently preserve the syntax. I'll need to inquire with the appropriate team internally to see when/if this limitation is to be addressed.

Alternatively, if you elect to follow a disconnected flow for your programmable object edits (i.e. edit the files directly and executing the T-SQL/deploying the solution), then the syntax will be preserved.

I hope this helps. Please let me know if you have any questions or concerns.

samssb82

I am also in the process of evaluating ReadyRoll for our organization and have this same issue / concern.  We have a separate system / process that handles database permissions so we are going to exclude them from our projects.  DROP CREATE will lose our permissions.  An option to allow for an IF NOT EXISTS CREATE THEN ALTER would be great.