Re-runnable SQL Scripts for DDL and DML

At some point of time in my career, I was in a role that I would prepare scripts for auto-refresh the database by running some SQL migration scripts, so that the database will match the latest binaries.

Concept

Here is my definition of re-runnable SQL scripts.

A SQL script is re-runnable if and only if the script will only have impact to the SQL server exactly once regardless how many times you run the script.

This is a very important aspect if we are able to re-run some upgrading scripts without worrying about what-to-run and what-not-to-run during developments or deployments. Let alone continuous integration.

Implementation

There is a pattern in re-runnable SQL script. I will try to document as much as I could, but if you could learn the pattern instead of the code, then you can use it in any DDL (Data Definition Language) or DML (Data Manipulation Language).

Add column to existing table

If not exists(Select 1 from sys.columns where Name = N'PublishDate' and Object_ID = Object_ID(N'BlogPost'))
Begin
 Alter Table dbo.BlogPost 
 Add PublishDate datetime NULL

 Print 'INFO - Column PublishDate is added for table BlogPost successfully.'
End
Else
Begin
 Print 'WARNING - Column PublishDate already exists for table BlogPost.'
End
Go

Pay attention to the if-else logic, we simply put our DDL inside a if clause. Too simple?

If we run the above script twice, you would expect it not running the second time.

As we can see in the result pane, the DDL will not run the second time. 

Create table

If not exists(Select 1 from sys.tables where Name = N'Fit')
Begin

 CREATE TABLE [dbo].[Fit](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Code] [varchar](50) NOT NULL,
  [Name] [varchar](255) NOT NULL,
  [SubjectToAcl] [bit] NOT NULL CONSTRAINT [DF_Fit_SubjectToAcl]  DEFAULT ((0)),
  [LimitedToStores] [bit] NOT NULL CONSTRAINT [DF_Fit_LimitedToStores]  DEFAULT ((0))
 ) ON [PRIMARY]

 Print 'INFO - Table Fit created successfully.'
End
Else
Begin
 Print 'WARNING - Table Fit already exists.'
End
Go

Add PK

If not exists(Select 1 from sys.key_constraints where parent_object_id = Object_id('Fit'))
Begin
 ALTER TABLE dbo.Fit 
 ADD CONSTRAINT PK_Fit PRIMARY KEY CLUSTERED (Id) ON [PRIMARY]

 Print 'INFO - PK Fit added successfully.'
End
Else
Begin
 Print 'WARNING - PK Fit already exists.'
End
GO

Alter an existing column NULL to NOT NULL

In this scenario, we are changing a column from nullable to non-nullable. Things get a little tricky here

The first if statement act as a precaution to check if the column exists, because we cannot modify a column if it does not exist and it will throw error and causing the script to stop.

The second if statement depends on what you are modifying from. If we were changing nvarchar(50) to nvarchar(max), we would have written it differently. Also, depending on your technical requirement, you may want to force the column to be nvarchar(max) regardless, or it may not be an issue at all. It really DEPENDS!!

If exists(Select * from sys.columns where Name = N'Keyword' and Object_ID = Object_ID(N'SearchTerm'))
Begin
 If not exists(Select * from sys.columns where Name = N'Keyword' and Object_ID = Object_ID(N'SearchTerm') and Is_nullable = 0)
 Begin

  Alter Table dbo.SearchTerm 
  Alter column Keyword nvarchar(max) NOT NULL

  Print 'INFO - Column Keyword is altered for table SearchTerm successfully.'
 End
 Else
 Begin
  Print 'WARNING - Column Keyword is already non-nullable for table SearchTerm.'
 End
End
Else
Begin
 Print 'ERROR - Column Keyword does not exists in table SearchTerm.'
End
Go

For argument sake, should I also be checking if table exists when I am adding a column to a table too? That's right. It will throw error if we try to add a column to a non-existing table. In my technical situation though, I know I am expecting the table there, but that would be a nice pre-caution. Therefore, it DEPENDS on your technical structure.

Alter an existing column TEXT to VARCHAR(MAX)

Slightly different approach to the above. I join the system types table to find out the definition of the column.

If exists( Select * from sys.columns C 
   join sys.types T on T.system_type_id = C.system_type_id
   where C.Object_ID = Object_ID(N'MyTable')
   and C.Name = 'Description'
   and T.Name = 'text')
Begin

 Alter Table dbo.MyTable 
 Alter column [Description] varchar(max) NULL

 Print 'INFO - Column Description is altered for table MyTable successfully.'
End
Else
Begin
 Print 'WARNING - Column Description is already not text type for table MyTable.'
End
Go

Insert Data with Identity Insert

Notice you must have the keyword go after turning identity insert on/off, but since you can't have go inside begin/end, the statements will be outside of the if-block.

SET IDENTITY_INSERT [dbo].[TaxCategory] ON 
GO

If not exists (Select 1 from TaxRate where TaxCategoryId = 6 and CountryId = 6)
Begin

 INSERT [dbo].[TaxRate] ([TaxCategoryId], [CountryId], [StateProvinceId], [Zip], [Percentage], [StoreId]) VALUES (6, 6, 0, NULL, CAST(10.0000 AS Decimal(18, 4)), 0)
 
 Print 'INFO - Data for TaxRate.[TaxCategoryId] = 6 and TaxRate.[CountryId] = 6 inserted successfully.'
End
Else
Begin
 Print 'WARNING - Data for TaxRate.[TaxCategoryId] = 6 and TaxRate.[CountryId] = 6 already exists.'
End
GO

SET IDENTITY_INSERT [dbo].[TaxCategory] OFF
GO

Update Data - Exact match

If not exists (Select 1 from Country where Published = 1 and TwoLetterIsoCode = 'AU')
Begin

 Update Country
 Set Published = 1
 where TwoLetterIsoCode = 'AU'

 Print 'INFO - Data for Country.Published = 1 for TwoLetterIsoCode = ''AU'' updated successfully'
End
Else
Begin
 Print 'WARNING - Data for Country.Published = 1 for TwoLetterIsoCode = ''AU'' already updated.'
End
GO

Update Data - Multiple matches

The if clause is slightly different to above. It checks for if exists not equal to instead of if not exists equal to. That is because we want to update all the records as long as there is one or more than one record is returned when the where clause match.

If exists (Select 1 from Country where Published = 1 and TwoLetterIsoCode <> 'AU')
Begin

 Update Country 
 Set Published = 0
 where TwoLetterIsoCode <> 'AU'

 Print 'INFO - Data for Country.Published = 0 for TwoLetterIsoCode <> ''AU'' updated successfully'
End
Else
Begin
 Print 'WARNING - Data for Country.Published = 0 for TwoLetterIsoCode <> ''AU'' already updated.'
End
GO

Delete with conditions

If exists (Select 1 from [ShippingMethod] where [Name] = 'Australia Post, Regular Parcels')
Begin
 Delete [ShippingMethod]
 where [Name] <> 'Australia Post, Regular Parcels'

 Print 'INFO - Data for ShippingMethod <> ''Australia Post, Regular Parcels'' deleted successfully.'
End
Else
Begin
 Print 'INFO - Data for ShippingMethod <> ''Australia Post, Regular Parcels'' already deleted.'
End
GO

Conclusion

.Net developers might already notice I adopted some of the .Net logging technique in the print messages. That's right, I would look for warning or error (nothing fatal here) after running my script. The initial investment of a deep level logging would pay off easily in future troubleshooting. This is something that I would not do 7 years ago when I was not influenced by web developments.

I hope I have demonstrated enough as an appetizer of what re-runnable SQL scripts look like.

There is no hard rock science about what and how many checks to put in the where clause. It is up to the judgement call of the developer.