I’m a big fan of SQL deployment scripts. I normally create deployment scripts for environments where I need to sync my SQL code and structures myself or pass along scripts to DevOps who can deploy on my behalf.
The reason for using scripts instead of a long-winded manual procedure is for consistency – ensuring all deployment steps are being followed and to eliminate the x-factor of human error and fatigue associated with late-night deployments.
There are two types of versioning related to managing SQL changes: state vs migrations.
Migrations refer to incremental changes since the last deployment whereas state refers to year-to-date changes. Both approaches have pros and cons. Scripts for migration type deployments are shorter and faster to deploy and deployment code is easier to review. Scripts for state style deployments are longer and take longer to execute. But the scripts are complete and independent from previous deployments.
This article shows examples of SQL change scripts specifically for SQL Server (on-prem and Azure SQL database) which is designed for migration style deployments.
Design principles
All examples are rerunnable and customizable. The first 5 examples are straight forward and use the metadata in the database to determine whether an object exists. And for SQL changes for code (view, functions, and stored procs) to create a stub if not exists then alter the object. This is to accommodate preexisting security changes that you don’t want to remove during a DROP operation.
/* Example 1: update a function */
if not exists(select * from sys.objects where [name]='fn_example' and [schema_id]=1)
BEGIN
exec ('CREATE FUNCTION dbo.fn_example() RETURNS TABLE AS RETURN (select GETUTCDATE() as col1)')
END
go
alter FUNCTION dbo.fn_example()
RETURNS TABLE AS RETURN (
select GETUTCDATE() as col1
);
go
/* Example 2: update a stored proc
*/
if object_id('dbo.[pr_RefreshDependentView]','P') is null execute('create proc dbo.[pr_RefreshDependentView] as select (1) as t')
go
alter proc dbo.[pr_RefreshDependentView]
as
select (1) as t
go
/* Example 3: check if column exists in table otherwise add column */
IF COLUMNPROPERTY(OBJECT_ID('dbo.table1'), 'fieldToAdd', 'ColumnId') IS NULL
BEGIN
alter table [dbo].[table1] ADD [fieldToAdd] nchar(3) NULL;
END
/* Example 4: creates a stub view if not exists */
if object_id('dbo.[myView]','V') is null execute('create view dbo.[myView] as select (1) as t')
go
alter view dbo.[myView] as select (1) as t
go
The trickiest part is dealing with changing table value types that are useful during ADF (“azure data factory”) development when you need to enhance the existing dataset with additional fields like a batchID or data source name that are not included with source data.
So the algorithm is to rename the old object, create the new, update dependencies and drop the old object.
/* Example 5: creates a stub view if not exists */
if exists(select top 1 1 from sys.types where name='myType' and [schema_id]=1)
BEGIN
EXEC sys.sp_rename 'dbo.myType', 'zMyType';
CREATE TYPE [dbo].myType AS TABLE(
[Shift] [varchar](8000) NULL,
[downtime] [varchar](8000) NULL,
[duration] [varchar](8000) NULL,
[varchar](8000) NULL,
[codeDesc] [varchar](8000) NULL,
[description] [varchar](8000) NULL,
[eqpNumber] [varchar](8000) NULL,
[componentCode] [varchar](8000) NULL,
[MyTimeDuration] varchar(8000) NULL,
[CustomerTimeDuration] varchar(8000) NULL
);
DECLARE @Name NVARCHAR(776);
DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.myType', 'TYPE');
OPEN REF_CURSOR;
FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sys.sp_refreshsqlmodule @name = @Name;
FETCH NEXT FROM REF_CURSOR INTO @Name;
END;
CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;
EXEC ('DROP TYPE dbo.zMyType');
END