CREATE OR ALTER instead of CREATE

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?

Best Answer

  • David AtkinsonDavid Atkinson Posts: 1,376 Rose Gold 2
    Accepted Answer
    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.
    David Atkinson
    Product Manager
    Redgate Software

Answers

  • OnanSaladOnanSalad Posts: 4 New member
    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.
  • 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. 
    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.