Monday, November 09, 2009

SQL Data Scripts

A standard practice in SQL Server development is to make any changes to objects or data using a script and apply it to the DEV system; once verified, it is applied to QA; once verified, it is finally applied to production.

You can take this one step further, and write your scripts to be deterministic - i.e. if the script is run multiple times, the outcome will be the same.

For example, let's say you have a lookup table tblSettings like this:

ID int IDENTITY (1,1),
ItemText nvarchar(50)

the table holds two records with ID's of 1 and 2, and you wish to add a third. You could simply write

INSERT tblSettings ( ItemText ) SELECT 'application author'

but if you execute that script more than one time, you get multiple records with that same text.

Now, this is a back-end table, and you have verified that the next ID will be 3, using this:

SELECT IDENT_CURRENT('tblSettings') + IDENT_INCR('tblSettings')

The solution is to test if that record exists before you insert the record, like this:

IF (SELECT 1 FROM tblSettings WHERE ID = 3 ) IS NULL
INSERT tblSettings ( ItemText ) SELECT 'application author'

in this way, the script can be run multiple times in DEV, QA or even production, and the outcome will always be that the table holds exactly those three records.

Now, let's say that you need to change the record just added to say 'application developer'. You could simply write

UPDATE tblSettings
SET ItemText = 'application developer'

but why "update" a record to the same value? The solution here is simply to include the old value in the criteria, like this:

UPDATE tblSettings
SET ItemText = 'application developer'
AND ItemText = 'application author'

The benefit of these methods is to touch the data only where necessary, and to ensure that the outcome is precisely what is expected.

No comments: