Writing better SQL scripts

Many organisations that have a platform for application hosting that includes different environments for development, testing and production use SQL scripts to migrate between environments. As a DBA I see a lot of these scripts and a lot of them are written very poorly while others (usually the generated ones) are small pieces of art.

The qualitity of scripts makes a huge difference in the time DBA's spend executing these scripts and as a result of that it influences the timeframe in which a script will be ran.

So, how to produce good scripts ? I'll give a few tips.

Note that code samples are all SQL 2000 compaitible. If your database uses SQL 2005 see the comment under 'Scripting other objects' on how to find out how to properly script your object(s) for that version.

Use a USE statement

A good script starts with a USE statement, this way you're sure your script will be executed in the right database. The reason for this is simple: it prevents your mutations from happening to the wrong database (usually the master database 😉 ). Another good practise is to set NOCOUNT to ON so the messages window isn't filled with 'rows affected' messages.

SET NOCOUNT ON

USE [<database name>]&nbsp;

Scripting Stored Procedures

The most important thing with writing good scripts is that your script
is repeatable on every environment no matter what version of the
database it runs. In my experience most changes to a database will be to stored procedures. The easiest way to ensure the stored procedure code you provide will end up being the new code is to use drop and create procedure statements, never use alter statements (these won&#39;t run if the procedure does not exist for some reason).

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N&#39;<stored procedure name>&#39;) AND OBJECTPROPERTY(id, N&#39;IsProcedure&#39;) = 1)
&nbsp; DROP PROCEDURE <stored procedure name>
GO&nbsp;&nbsp;&nbsp;

CREATE PROCEDURE <stored procedure name>
<parameters>
AS
BEGIN
&nbsp; ……
END

It&#39;s also a good practise to use BEGIN and END statement combined with indentation so it&#39;s obvious where your procedure starts and ends in case there&#39;s multiple stored procedures in scripts.

Scripting other objects

If you&#39;re curious how to properly script other objects you can check easily by setting the &#39;Include IF NOT EXISTS clause&#39; option (Options->Scripting) in SQL Server Management Studio to TRUE and right click an object of the type and select a Script … as -> CREATE To option. There&#39;s one important exception to the &#39;don&#39;t alter, but drop if exists before you create&#39; guideline and that&#39;s the TABLE object. Your tables hold data, you probaly do not want this data lost.

Use transactions

You probably want your update to succeeded as a whole or not at all. Using repeatable scripting will make it less important, but you still don&#39;t want your system to be in some kind of version between version X and Y, you want it version X or Y. The solution is simple and native to a DBMS: use transactions ! If you&#39;re using SQL 2005 it&#39;s very easy to implement with TRY.. CATCH ROLLBACK constructions if you&#39;re using SQL 2000 test the results of your operations with @@ERROR and end operation with a GOTO rollback block statement.

Indentation and comments

Make your scripts readable by humans and not just for a SQL parser. Use comments so it&#39;s obvious what is happening and use proper indentation.&nbsp;

Finally if you want to show off….

Give status updates using PRINT messages. Provide information on what objects were created,deleted or altered and give numbers on the affected records of insertions, deletions or updates using @@ROWCOUNT. It&#39;s very helpful if something goes wrong, which won&#39;t happen because you&#39;ve tested your scripts thoroughly…….. right ?