ALTER as well as CREATE scripts
It looks like each commit adds a CREATE script. To be really useful in a continuous integration environment, we'd need ALTER scripts. Ideally each commit would generate a CREATE for the complete object, and an ALTER from the previous revision.
SQL Source Control 3.0 is now available!
It includes a new feature called ‘Migrations’ that will help avoid data loss when renaming a table as it allows you to create, store and share SQL migration scripts for accurate deployment.
For more information about Migrations, please visit:
It would be good if the scripts were created as a CREATE/ALTER instead of just creates.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[my_procedure]') AND type in (N'P', N'PC'))
exec sp_ExecuteSQL N'CREATE PROCEDURE [dbo].[my_procedure] AS
SET NOCOUNT ON
-- procedure stub
ALTER PROCEDURE [dbo].[my_procedure]
SET NOCOUNT ON
-- full procedure text
An Early Access build of SQL Source Control 3.0 is available which inlcudes a new feature called "SQL Migrations".
This feature allows you to create, store and share SQL migration scripts for accurate, automated deployment using SQL Source Control and SQL Compare.
The installer zip can be found here:
As I said, this is an Early Acces build - so does not include all the functionality we are hoping to provide with "SQL Migrations". We'd love to hear feedback from you regarding whether this feature provides a good solution to the problem you've described.
Eric Wahner commented
My take on this is that you should just give us the ability to customize the script that you are versioning. Some want CREATE and myself I always want an ALTER. For me if the ALTER succeeds on my production environment then I know I don't need to research the security privs. It's also nice that you have a tool like SQL Compare, but if we don't have this tool we will need to be able to work with the source in a different way, like using a custom MS-BUILD script.
Leach - very good point and it's one that we've been pondering recently. We are very much thinking of allowing the developer to override the script generated by 'get latest' and submit their own to be used instead. This would be saved in source control and associated with two versions. This would mean that not only object renames could be addressed, but also any type of data migration or complex refactoring. SQL Compare would also honor these custom migration scripts and use them instead of generating its own.
Renames are not the only case where there is a need for alter scripts rather then create scripts
sometimes data format for the application is changed - and an alter script is generated by the developer to support such a change in the data
sometimes changes are made gradually building one alter on top of the other
is there any way to link such an alter script to a commit and have it as part of the generated script when passing from previous to that version?
or are scripts like this have to be kept as stored procedures (that are versioned) and controlled by some DB version value in some control table?
Rob - Good point! Please vote/comment on this issue, https://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/867961-treat-renames-as-renames-instead-of-a-drop-and-an-.
Richard - I hope you got an answer to your question. SQL Source Control is part of the SQL Developer Bundle (and the SQL Toolbelt).
The Create scripts are perfect as a diff and blame source: I can see what changed when, and Sql Compare can generate change scripts to push these changes into place.
However, if I rename a column, Sql Compare will see the need to delete an extra column and create a new column. Optionally (or always) checking in alter scripts as well allows CI to understand the developer's intentions, and keep the data in the row. Getting Sql Source Control to know it was renamed is likely the elephant in the room though.
Richard Dudley commented
Gotcha! Will SQL Source Control be part of the Developer Bundle?