CREATE OR ALTER instead of CREATE

OnanSalad

We're evaluating SQL Source Control against our current source control process and one of our requirements is that SQL object scripts in the repository use CREATE OR ALTER where applicable instead of the default CREATE.  I expected to find this under "Comparison Options | Behavior," but I'm not seeing it there.  Is SQL Source Control not compatible with CREATE OR ALTER syntax for applicable objects like stored procedures, functions, and views?

David Atkinson

Hi - thanks for evaluating SQL Source Control. You're right to look under comparison options, as this is where the option would be, as it inherits from capabilities offered in SQL Compare. Unfortunately, SQL Compare doesn't offer this just yet, but it's on the roadmap and I'd expect it early next year. 

What is the reason you need CREATE OR ALTER today? Even if the scripts are stored as CREATEs, these will be converted to ALTERs as needed when you deploy the changes from source control to your target databases using SQL Compare.

OnanSalad

Hi David - our original system used the "IF EXISTS....DROP" construct for preexisting objects.  After upgrading to SQL16 with querystore we switched to CREATE OR ALTER to keep forced execution plans when possible.  If the resulting sql compare script substituted ALTER then it should accomplish the same thing.

David Atkinson

Yes, this is how it works. SQL Source Control scripts out all objects as CREATE, not because this is reflective of how we apply changes, but because we had to pick something as a way of storing the definition of the objects in version control. If you load the scripts folder (with their CREATEs) into SQL Compare and create a deployment script, this will generate the appropriate ALTERs based on the differences between the source and targets. 

Koenraad.Dendievel

I'm using SQL source control in conjunction with other tools like beyond compare and opening the .git folder in solution explorer ( for fast deploy / testing) where the option to have the scripts be created with CREATE OR ALTER would be very handy.

I also run into this problem doing major refactorings (using SQL Compare to script out specific objects - comparing it to an empty database and doing text replace etc on the scripts) where i have to change all create statements to alter statements.

Any updates on the roadmap?

Kendra_Little

Hi @Koenraad.Dendievel,

Thanks for letting us know about your use case. This enhancement is up for consideration and I've updated the request with the use case info you provided. It's just up for consideration and not something we're actively working on presently, but understanding customer impact definitely helps us.

Kendra

OnanSalad

A year later and I'm running into this same issue but now with SQL Compare.  I have a sql repo reconciliation report that uses SQL Compare to show all objects with sql discrepancies between the repo and the database.  CREATE OR ALTER is the preferred way to implement sql changes in our shop, so this report is returning many false positives because of CREATE vs CREATE OR ALTER.  I wish there was an ignore option like "Ignore CREATE\ALTER\CREATE OR ALTER."  

RichardReynolds

Hi RG, are there any updates on this, it is affecting us as well using V14 in 2023. SQLCompare is inconsistently reporting a difference on the CREATE OR ALTER syntax. Eg. It has flagged 1 function with this difference even though there are > 100 other objects with that syntax in the repo that it is not flagging. Like others, we want to use the CREATE OR ALTER to prevent losing specific object permissions as well as to prevent the loss of forced plans where they exists. Also, why not take advantage of the SSMS SMO that will allow you to script files as CREATE OR ALTER?

SteffenCHBeck

Is there a possibility to vote for this feature?
Right now I wanted to regenerate some scripts because SQLCompare shows differences - for instance because of trailing semicolon after END of Stored Procedure.
Regenerating script with SQL Compare removes existing "OR ALTER" statements even if there was no difference before. Code Reviewers ask why I removed this. 

David Atkinson

@SteffenCHBeck - can you confirm if "Add CREATE OR ALTER for rerunnable scripts where possible" is checked in SQL Compare?

SteffenCHBeck

@David Atkinson, yes I can confirm "Add CREATE OR ALTER for rerunnable scripts where possible" is checked

David Atkinson

@SteffenCHBeck
Are you generating a deployment script, or are you creating a "scripts folder" from a database? 

SteffenCHBeck

@David Atkinson I'm creating a scripts folder from database (with Version 15.3.6.25729)

David Atkinson

@SteffenCHBeck - The option only applies to generated deployment scripts. The scripts folder is our on-disk representation of your schema model. These scripts are to show you the state of the objects in the database, and aren't meant to be run. Can you describe how you are using the tool and the scripts folder functionality?

SteffenCHBeck

@David Atkinson,
for regular deployments to staging and production we use deployment scripts generated by SQLCompare. Deployment Scripts are executed by liquibase containers in Kubernetes.
(We did not check if switching to free flyway version will have advantages. Liquibase is company standard, used by other teams which are not using Toolbelt&SQLCompare, too)
For development of existing Procs/Views we open file from scripts folder, modify and execute on development instance. Thus Procs,Views,... need to have CREATE OR ALTER. New Procs/Views often are created in development instance and later are generated to scripts folder with SQLCompare
Sometimes it's necessary to create existing scripts back to scripts folder, for instance because of tiny differences in the database representation.

David Atkinson

@SteffenCHBeck - Do you compare the scripts folder to production to generate the deployment scripts that are later deployed with Liquibase? 

Flyway Teams allows for a workflow whereby you can modify the schema model (the name for the scripts folder in Flyway) and apply changes from the schema model to the database. You can also make changes in the database and sync these back to the schema model with just a button click. Behind the scenes SQL Compare tech is being used to generate the right script to apply changes to and from the schema model. These schema model scripts are only meant as a definition of the schema state and not intended to be run directly against a database.