In this article, I want to share my thoughts about writing SQL scripts. Those experiences are taken from about 3 years of developing and deploying .NET applications, that store their data in MS SQL Server. This article is divided into three parts:

  • Definition – Definition of “SQL Reusability”.
  • Schema Reusability – Examples of how to change database schema in a good way.
  • Data Reusability – Examples of how to change data in a good way.
  • Useful cheats – Collection of scripts that would help you in writing migration scripts.

Part 1: Definition:

SQL Reusability – SQL script’s feature that guarantees that:

  • Script can be executed more than once without duplicating objects (schema + data).
  • Script can be executed more than once without throwing exceptions (ex. because of duplications).
  • Execution can be started on database in unkown state (or state from previous release).
  • Execution guarantees that database will be modified to desired state (schema + data).

Part 2: Schema Reusability:

This refers not only to ‘migration’ but also to ‘create database’ scripts. Here are some examples:

Check if table exist:

if exists (select * from sys.tables where name like 'tableName') begin
    -- Manipulate with table
    print 'Table modified'
end else begin print 'Done' end

Check if column exists:

if exists (select column_name from information_schema.columns where table_name = 'tableName' and column_name = 'columnName')
    -- Manipulate with column
    print 'Column modified'
end else begin print 'Done' end

Checking if collumn is nullabe:

if 1 = select columnproperty(object_id('tableName', 'U'), 'columnName', 'AllowsNull') begin
    -- Manipulate with column
    print 'Column modified'
end else begin print 'Done' end

Check if foreign key exists:

if exists (select * from sys.foreign_keys where object_id = object_id('foreignKeyName') and parent_object_id = object_id('tableName')) begin
    -- Manipulate with foreign key
    print 'Foreign key modified'
end else begin print 'Done' end

Check if other object exist:

if exists (select * from sys.objects where object_id = object_id('objectName')) begin
    -- Perform some actions
    print 'Object modified'
end else begin print 'Done' end

Part 3: Data Reusability:

This refers mostly to default data or configuration data tables (dictionaries). Idea is to follow these rules:

  • Use ID column as primary key and clustered index (int preffered rather than uniqueidentifier). This column will be used for creating relations to other tables. Values don’t have to be human readable and known to the user – can be generated automatically.
  • Use Symbol column as row identifier for scripts. Value can be human readable, and have to be unique.

Table example:

create table [Configuration](
    [ID] int not null identity(1,1) primary key clustered,
    [Symbol] nvarchar(10) not null unique nonclustered
    -- other columns
)

Reusable update data script example – upsert operation:

declare @symbol nvarchar(10)
declare @value nvarchar(100)
set @symbol = 'Timeout'
set @value = '130'
if not exists (select * from [Configuration] where [Symbol] = @symbol) begin
    insert into [Configuration] ([Symbol], [Key]) values (@symbol, @value)
    print @symbol + ' inserted.'
end else begin
    update [Configuration] set [Key] = @value where [Symbol] = @symbol
    print @symbol + ' updated.'
end

Part 4: Useful cheats

Modifing identity keys:

set identity_insert [tabeleName] on
insert [tableName] ([IdentityColumn], [col2], [col3], ...)
values ([IdentityValue], [col2value], [col3value], ...)
set identity_insert [tableName] off

Disabling/enabling all constraints (all except of unique and clustered key):

alter table [tableName] nocheck constraint all
alter table [tableName] check constraint all

Disabling/enabling specific constraint:

alter table [tableName] nocheck constraint [constraintName]
alter table [tableName] check constraint [constraintName]

Disabling/enabling trigger:

disable trigger [triggerName] on [tableName]
enable trigger [triggerName] on [tableName]

Use dynamic SQL statements:
In case you dont want to break existing transaction (GO statement will break it).
This will fail on line 4, because interpreter will not find column [NewCol]:

declare @someValue int
set @someValue= 0
alter table [tableName] add [newCol] int null
update [tableName] set [newCol] = @someValue

This will work:

declare @someValueint
set @someValue= 0
alter table [tableName] add [newCol] int null
exec('update [tableName] set [newCol] = ' + @someValue)