Best Practices for the Management of SQL Scripts

I have been a long time fan of writing scripts for every action that is taken to make any changes in a database. This means that version control tools can be used to track the changes and if necessary repeat them.

In previous roles I have worked in environments where it has been essential to have the ability to deploy identical alterations to multiple environments. The types of alteration that have ben required have included:

• Creating databases, including all objects within them
• Making enhancements to databases
• Bug fixes
• Optimisation enhancements
• Configuration changes

All of these alterations have at least one script associated with them. The bigger the alteration, the more likely that multiple scripts will be required. For example, when it comes to creating a database, I prefer, in most, but not all cases, to have separate scripts for each object:

• Database
• Schema
• Table
  • Creation
  • Default data insert
  • Primary key
  • Foreign keys
  • Indexes
  • Unique Constraints
• Trigger
• View
• Function
• Stored Procedure

Most other types of alterations will consist of only two scripts:

• Make the changes
• Roll back the changes

The naming conventions I use and the structure within which I store these scripts is as follows:

• \SQL
  • \Database – Scripts for creating and altering databases
    • \{DatabaseName}.create.sql
    • \{DatabaseName}_{AlterationDescription}.alter.sql
  • \Function – Scripts for creating and dropping user defined functions
    • \{DatabaseName}.{SchemaName}.{FunctionName}.create.sql
  • \Job – Scripts for creating Jobs. These include creating the relevant categories and job steps.
    • \{JobName}.create.sql
  • \Schema – Scripts for creating and altering schemas
    • \{DatabaseName}.{SchemaName}.create.sql
    • \{DatabaseName}.{SchemaName}_{AlterationDescription}.alter.sql
  • \SP – Scripts for dropping stored procedures
    • \{StoredProcedureName}.create.sql
  • \Table – Scripts for creating tables, and creating table-dependent objects
    • \{DatabaseName}.{SchemaName}.{TableName}.create.sql
    • \{DatabaseName}.{SchemaName}.{TableName}.insert.sql – Insert default data required when the database is built, e.g. lookup values.
    • \{DatabaseName}.{SchemaName}.{TableName}.pk.sql – Create the primary key for the table
    • \{DatabaseName}.{SchemaName}.{TableName}.key.sql – Create the foreign keys for the table
    • \{DatabaseName}.{SchemaName}.{TableName}.uc.sql – Create a unique constraint on a table
    • \{DatabaseName}.{SchemaName}.{TableName}_{TriggerAction}.trigger.sql
  • \View – Scripts for creating and dropping views
    • \{DatabaseName}.{SchemaName}.{ViewName}.create.sql

Depending on the size of the database, the table scripts may be split into separate directories.

At times when the scripts are run to create a database, or to drop and recreate a table it can be very useful to have stored procedures that can, for example list and drop all foreign keys that reference a primary key or table before it is dropped. It is a bonus if these procedures can be used from anywhere. I will cover my own custom sp_ procedures in a later post.

To deploy scripts, I have used a variety of tools ranging from batch scripts using OSQL, ISQL, SQLCMD to bespoke applications designed and built specifically to run scripts. I will go into the pros and cons of different mechanisms for running scripts in future posts.