It doesn’t happen very often but I recently needed to add a default value to an existing column.  Of course MSDN provided me everything I needed but figuring it out from the railroad diagrams took a few tries.

To add to the confusion, the command for SQL Server Compact Edition is different than it is for for Enterprise.  Of course I found Compact Edition code first but needed Enterprise, not knowing they were different.

The code for COMPACT edition follows…  It’s untested…

-- Compact edition.

ALTER TABLE f_response ALTER COLUMN new_contact_flag SET DEFAULT 0;

The code for Enterprise edition… tested…

-- SQL 2005 Enterprise
ALTER TABLE f_response ADD CONSTRAINT new_contact_flag_def
            DEFAULT 0 FOR new_contact_flag;

The next time I need to add a default constraint, here it is.

What discoveries have you made that are worth saving?

Stay curious… keep learning

Dave