Managing large SQL databases its important to keep your updates transactional and as non-blocking as possible to keep the up time high.

When deploying incremental change you can do it in a way that your changes are backwards compatible with old code, and have less of an impact.

A lot of organisations I’ve worked with pre-deploy their db changes long before their code to make cut over seemless, here a few practical tips on making you DB changes backwards compatible and low impact.

TABLES

Tables are easy for schema changes:

• Never Change your column order
• Never remove a column (if you don’t need it then ignore it)
• Never change a datatype, if you “really” need to, then create a new column and migrate the old data
• When adding new columns always use nullable types (Where possible)

The last point though really depends on the size of your table, when you add a new column that is nullable, even to massive tables, the transaction will complete in milliseconds. If you set NOT NULL you will need to specify a default, which will cause it to essentially update every row in the table with the new value, on large tables this can takes minutes or even hours depending on your table size and capacity.

Below is an example of a “good” update, nullable

ALTER TABLE dbo.affiliate ADD addressStreet nvarchar(255) NULL
 GO

Applying Indexes on the fly is usually ok for small to mid size table that aren’t under heavy transacitonal load, but if you have high OLTP load on large or massive tables you might want to arrange for an outage window to apply the indexes, or at the very least schedule them or over night, when the stakeholder won’t notice your applications stop responding for several minutes 🙂

If a web site goes offline and no one is there to see it, is it really offline?

Adding constraints on the fly or FKs with constraints you’ll need to consult with another source, i don’t use constraints a lot because they put a lot of potential load/risk on OLTP tables which i work with a lot.

PROCEDURES

Procedures are pretty straight forward to:

• Never remove a parameter
• Always assign a default to a new parameter

The last point there begs the question though, What should the default be?

90% of the time its going to be null, lets take a look at a few basic examples.

1. Procedure that runs an Update

If we look at this code example

CREATE PROCEDURE dbo.UpdateAffiliate
 @affilaiteId int,
 @affiliateName VARCHAR(255)
AS
UPDATE affiliate
SET affiliateName=@affiliateName
WHERE affilaiteId=@affilaiteId

First we need to think about the code that calls the proc we are updating, so that the both the old and new code can call the proc and not fail. here is an example of calling code.

EXEC UpdateAffiliate @affilaiteId=1,@affiliateName'Big Affiliate'

If we are to add a new parameter we simply do so by adding a default value

CREATE PROCEDURE dbo.UpdateAffiliate
 @affilaiteId int,
 @affiliateName VARCHAR(255),
 @addressStreet VARCHAR(255)=null
AS
UPDATE affiliate
SET affiliateName=@affiliateName,
addressStreet=@addressStreet
WHERE affilaiteId=@affilaiteId

This means that when the old code calls the proc it will still run, and simply sets the value to null.

Where this doesn’t work is when you are going to run both version in parallel for an extended period.

If that is the case using the above example, when someone updates the affiliate record with the new code

EXEC UpdateAffiliate @affilaiteId=1,@affiliateName'Big Affiliate', @addressStreet='12 MyStreet Road'

The a user update is using the old version

EXEC UpdateAffiliate @affilaiteId=1,@affiliateName'Big Affiliate'

This will cause the new field (addressStreet in this case) to be set to null.

If you are doing a cut over this is not an issue though.

the solution to this is to add a case

CREATE PROCEDURE dbo.UpdateAffiliate
 @affilaiteId int,
 @affiliateName VARCHAR(255),
 @addressStreet VARCHAR(255)=null
AS
UPDATE affiliate
SET affiliateName=@affiliateName,
addressStreet=CASE @addressStreet WHEN NULL THEN addressStreet ELSE @addressStreet END
WHERE affilaiteId=@affilaiteId

This will work, unless you actually need to set the field to NULL for some reason, in this case you will need to use a value that the field will never be set to as the default, for example a tab space, then use this in your case statement.

2. Procedures that returns data.

For select statement it depends on what you code is on the other end but most of the time if you stick to these rules you’ll be ok

• Don’t Change column names (not even case)
• Don’t reorder columns
• Only add new columns

Most languages when they handle data will simply ignore new columns that are added, some languages are case sensitive with their column handling and some developers are mentally unstable and use ordinals instead of the column names.

3. Procedures that INSERT data

Inserting is a little easier than updating, again just use a default value. And if your column has a defualt value this should be the default in your proc, not null as in the above example.