Wednesday, June 16, 2010

Things I like – generate DROP .. CREATE scripts

I will be writing about things I like from time to time. That is SQL Server things - other than beer, good books and company.
For today, one small thing but how useful. In SQL Server Management Studio 2008 you can script objects as DROP and CREATE in one go:
This is very useful for us, as we store all database objects in our SVN as DROP..CREATE scripts. Using this feature saves quite a bit of time each time we want to update the source control.
I noticed an interesting behavior - if you script your table this way, all constraints defined in the table are scripted with drop statements before the actual DROP TABLE statement is included. This is quite handy as you can easily modify this script to change constraint names or definitions without having to script them separately.

Sunday, June 13, 2010

Coding practices - Call your constraints

I wrote a few weeks ago about unit tests and good practice of naming your constraints. I think that most of the developers are used to call primary keys and unique constraints, sometimes foreign keys. But in most of the code I’ve seen the CHECK and DEFAULT constraints remain unnamed. You might wonder, why bother? The answer is that as long as you don’t have to drop the constraints, everything is OK – apart from cryptic error messages if the constraints are violated. The problem begins if for some reason you need to drop the constraint – for example to change range of the allowed values or default value of a column. The other scenario is also painful – if you want to compare two databases and you script out objects with constraint names. If you let SQL Server to name your constraints, each database you create from your scripts is different. And you have different error messages in production than in UAT for example. Not a very good idea for troubleshooting. Let’s suppose you create a table for storing order summaries.
   1: create table OrderSummary
   2: (
   3: OrderID int not null,
   4: OrderValue decimal(10,2) not null,
   5: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
   6: OrderDate datetime not null default getdate()
   7: )
Looks nice. But to drop the constraint on the OrderCurrency column, you have to use dynamic sql:
   1: select * from sys.check_constraints
   2: declare @sql nvarchar(200)
   3: select @sql = N'alter table OrderSummary drop constraint ' + 
   4:         quotename((select name from sys.check_constraints 
   5:             where parent_object_id = object_id('OrderSummary')))
   7: --print @sql
   8: exec sp_executesql @sql
Please note that the above script works only if you have one unnamed check constraint. If you have more constraints, you have to write more complex code. Not a very nice option if you want to have some control on which constraints you want to drop and which are to stay. Now if you run the following script, you will see that each time you create the table, names of constraints are different:
   1: use master
   2: go 
   3: create database devDB
   4: go
   5: use devDB
   6: go
   7: --simulate various test objects creating and dropping in DEV database
   8: create table OrderSummaryTest
   9: (
  10: OrderID int not null,
  11: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  12: OrderDate datetime not null default getdate()
  13: )
  14: drop table OrderSummaryTest
  16: go
  18: create table OrderSummary
  19: (
  20: OrderID int not null,
  21: OrderValue decimal(10,2) not null,
  22: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  23: OrderDate datetime not null default getdate()
  24: )
  25: go
  27: select convert(varchar(10), db_name()) [test], 
  28:     name from sys.check_constraints 
  29:     where parent_object_id = object_id('OrderSummary')
  31: go
  33: create database prodDB
  35: go
  37: use prodDB
  39: go
  41: create table OrderSummary
  42: (
  43: OrderID int not null,
  44: OrderValue decimal(10,2) not null,
  45: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),
  46: OrderDate datetime not null default getdate()
  47: )
  49: go
  51: select convert(varchar(10), db_name()) [test], 
  52:     name from sys.check_constraints 
  53:     where parent_object_id = object_id('OrderSummary')
  55: go
  57: ----cleanup
  58: use master
  59: go
  60: drop database devDB
  61: drop database prodDB
  62: go
As you see, the constraint names are different and UGLY. Note that I simulated object creation and destruction in devDB, because if you comment lines 7 – 14, you may get the same object ids for the constraints. But this never happens in real life. If you want to avoid such problems and make your and your dba’s life easier, give your constraints names, like in the following script:
   1: create table OrderSummary
   2: (
   3: OrderID int not null,
   4: OrderValue decimal(10,2) not null,
   5: OrderCurrency char(3) constraint CHK_OrderCurrencyRange check (OrderCurrency in ('EUR', 'USD')),
   6: OrderDate datetime not null constraint DF_OrderDate default getdate()
   7: )
This way, to drop the default constraint you just have to call:
   1: alter table OrderSummary drop constraint [DF_OrderDate]
No dynamic SQL required, and code is 100% same in production and in DEV. To sum it up, if you get the habit of assigning names to ALL your constraints, your code will look better and others will be able to understand it faster, what is important when troubleshooting production issues.