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. |
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
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)
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
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
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.
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.
