How to create SQL Deployment Script

How to create SQL Deployment Script

1.  Introduction

Deployment is one of the important aspect in software industry. Developers  generally face lots of script execution failure issues on Quality Assurance(QA), User Acceptance Test(UAT) and Production server(PROD). These script execution failure issues occur due to improper preparation of deployment script. In this article, I am going to explain the how to prepare SQL deployment script. In below article I have explained the naming conventions and standard to prepare the deployment script. This article helps you to minimize/nullify the chances of deployment error.

2.   Description

Below tabular chart shows the naming conventions used for the SQL table constraints and indexes, Views, Procedures, Triggers, Function and SQL Jobs/Schedulers. Let us take an example having database name as “OrderProcessing”. First always use camel case conventions for all database objects like Database Name, Constraints and Indexes, Views, Procedures, Functions and SQL Jobs
Suppose,
Database Name: OrderProcessing
Then the Short name of Database: OP_
Schema Type
Schema Name
Naming Convention to Use
Example
Constraints
Foreign Key
FK_TableName_ColumnName
FK_Order_Id
Index
IX_TableName_ColumnName
IX_Order_Id
Primary Key
PK_TableName_PrimaryKeyColumnName
PK_Order_Id
Check
CK_TableName_ColumnName
CK_User_Email
Unique
UQ_TableName_ColumnName
UQ_User_UserNameEmail
Programmability/View/Management
View
OP_View_ProjectModuleName_ViewName
OP_View_Payment_PaymentDetails
Procedure
OP_Proc_ProjectModuleName_ProcedureName
OP_Proc_Employee_Insert
OP_Proc_Employee_GetAll
OP_Proc_Employee_GetById
OP_Proc_Employee_Update
OP_Proc_Employee_Delete
Trigger
OP_Trigger_TableName
OP_Trigger_Employee
OP_Trigger_Address
Function(Scalar & Table Value)
OP_Fn_FunctionName
OP_Fn_Split
OP_Fn_ModuleName_FunctionName
OP_Fn_Payment_PaymentDetails
OP_Fn_KeyValue
Job/Schedule
OP_Job_JobWork_JobName
OP_Job_Email_AdminWeeklyStatusReport
OP_Job_Email_ManagerDailyStatusReport
OP_Job_Email_WelcomLetter
OP_Job_Schedule_UpdateDeliveryStatus
Database
                While preparing of any script set the database name where you want to execute the script.
USE[DatabaseName]
GO
USE[OrderProcessing]
GO
Constraints
                Always check the constraint exist in your database
                        IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = ‘ForeighKeyName’)
BEGIN
          — Create foreign key constraint on table
END
GO
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = ‘ForeighKeyName’)
BEGIN
          — Drop foreign key constraint on table
END
GO
Table
Always check the table exist in your database   
                        IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘TableName’)
BEGIN
          — Create Table Syntax
END
GO
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘TableName’)
BEGIN
          — Drop/Alter Table Syntax
END
GO
ADD/DROP Column
                Always check the column exist in your table
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE name = ‘ColumnName’ AND OBJECT_ID = OBJECT_ID(‘TableName’))
BEGIN
       — ALTER TABLE TO ADD COLUMN
END
GO
                        IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘Address1’)
BEGIN
          — ALTER TABLE TO ADD COLUMN
END
GO
       
IF EXISTS(SELECT 1 FROM sys.columns WHERE name = ‘ColumnName’ AND OBJECT_ID = OBJECT_ID(‘TableName’))
BEGIN
       — ALTER TABLE TO DROP COLUMN
END
GO
                        IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘Address1’)
BEGIN
          — ALTER TABLE TO DROP COLUMN
END
GO
Alter Column
                Always check the column exist in your table
                        IF EXISTS(SELECT 1 FROM sys.columns WHERE name = ‘ColumnName’ AND OBJECT_ID = OBJECT_ID(‘TableName’))
BEGIN
          — ALTER TABLE TO CHANGE DATATYPE
END
GO
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘ColumnName’)
BEGIN
          — ALTER TABLE TO CHANGE DATATYPE
END
GO
Add or Update Master Data
                This is also important script generally developers do mistakes. If you do not check data you want to insert, then there would be more chances of duplicating the data.
IF NOT EXISTS(SELECT 1 FROM TableName WHERE ColumnName = ‘ValueToCheck’)
BEGIN
       — Insert Query
END
GO
IF EXISTS(SELECT 1 FROM TableName WHERE ColumnName = ‘ValueToCheck’)
BEGIN
       — Update Query
END
GO
IF NOT EXISTS(SELECT 1 FROM Country WHERE Name = ‘India’)
BEGIN
       INSERT INTO Country(Name)
       VALUES(‘India’)
END
GO
IF EXISTS(SELECT 1 FROM Country WHERE Name = ‘UK’)
BEGIN
       UPDATE Country
       SET Name = ‘United Kingdom’
       WHERE Name = ‘UK’
END
GO
Procedure
          Always check the procedure exist in your database
IF NOT EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘ProcedureName’) and [type] = ‘P’)
BEGIN
       — Create procedure
EXEC sp_recompile N’DatabaseName.StoredProcedureName’;       
END
GO
IF EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘ProcedureName’) and [type] = ‘P’)
BEGIN
       — Alter/Drop procedure
EXEC sp_recompile N’DatabaseName.StoredProcedureName’;
END
GO
It would be good practice to recompile the stored procedure you created or altered. For this you can use below syntax after creating or altering the stored procedure.
               
Function
                Always check the procedure exist in your database
IF NOT EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘FunctionName’) and [type] = ‘FN’)
BEGIN
       — Create function
END
GO
IF EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘FunctionName’) and [type] = ‘FN’)
BEGIN
       — Alter or drop function
END
GO
Views
Always check the view exist in your database
IF NOT EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘ViewName’) and [type] = ‘V’)
BEGIN
       — Create View
END
GO
IF EXISTS(SELECT 1 FROM sys.objects where object_id = object_id(‘ViewName’) and [type] = ‘V’)
BEGIN
       — Alter or Drop View
END
GO
Database Schedule/Job
Always check the job exist.
IF NOT EXISTS(SELECT JOB_ID FROM msdb.dbo.sysjobs_view WHERE name = N’OP_Job_JobWork_JobName’)
BEGIN
       — Create Job
END
GO
IF EXISTS(SELECT JOB_ID FROM msdb.dbo.sysjobs_view WHERE name = N’OP_Job_JobWork_JobName’)
BEGIN
       — Delete job
       EXEC msdb.dbo.sp_delete_job @job_name = N’OP_Job_JobWork_JobName’, @delete_unused_schedule = 1
END
GO

3.  Typical Deployment Script

Whenever you creating any SQL Deployment script you need to follow the sequence of scripts also. The typical deployment script uses below sequence so that the dependent object would not fails.
1.       Create table script
2.       Alter table script
3.       Insert or Update Master data script
4.       Create/Alter Functions
5.       Create/Alter Views
6.       Create/Alter Procedures
7.       Create/Alter Triggers
8.       Create SQL Jobs/Schedules

4.  Summary

In this article we learned the standards we need to follow while preparing any SQL deployment script.  If you have any comments or suggestion to improve this article I always welcome.

Leave a Reply