Digital Earth Weblog

Today was almost a WTF day. The goal was to replicate selected tables from one SQL Server 2000 database to another. Here are two simple hints that will help ensure that replication won’t break your applications.

1. Every table must, must, must have a primary key.

SQL Server’s transactional replication requires that each table being replicated have a primary key, as any well designed database should. Even better, make sure your tables are normalised. If you don’t know what “database normalisation“ means then please take the time to learn. (I’m no expert, but I can tell you that a lot of things become much easier to do in a well normalised database.)

Sadly, none of the 13 tables that had to be replicated had a primary key. We ended up adding surrogate primary keys by adding an autoincrementing integer column to each table. This hack would not have been necessary in a well designed database.

2. Never use the * wildcard to select all columns in SELECT queries.

SQL Server’s transactional replication adds a version column to each table being replicated. If you have any SELECT queries in stored procedures, views or application code that look like

[sql]SELECT * FROM table_name[/sql]

then there is a good chance that something will break when that version column is added. Always specify the names of the columns in your SELECT queries.

In this case a large stored procedure was using column wildcards to select data into temporary tables that no longer had enough columns. This query has the same issue because the source table will likely have more columns than the target table:

[sql]INSERT INTO target_table_name FROM (SELECT * FROM source_table_name WHERE column_a = ‘foobar’)[/sql]

The views seemed OK, but the only way to check the applications that use this database is to search their source code.

Simple practices can save you a lot of hassles in the long run, particularly if your database is used by more than one application.

• Normalise your tables,
• use primary keys,
• use referential integrity and checks,
• always name columns in queries,
• use indexes wisely, and
• use consistent naming conventions.

Most of the above have exceptions, but unless you know why the exception is valid stick with the basics. Your life as a database application developer will be much better. And, any developers who have to maintain your applications are less likely to be seen with voodoo dolls bearing your name.