Monday, June 1, 2009

MS SQL / ALTER TABLE: Add a new column with default values

While not an every day occurence, I often need to add columns to an existing sql server table. But it is a bit of a pain if the new column should not allow nulls. Fortunatelly, sql server provides the WITH VALUES clause for just such a situation. Because I can never remember the exact syntax (when I most need it), here is an example to jog my memory next time ;)

  
--- Change "BIT" to whatever data type is needed
--- and "0" to whatever default value is desired
ALTER TABLE MyTable ADD MyNewColumName BIT NOT NULL
CONSTRAINT MyConstraintName
DEFAULT 0 WITH VALUES

1 comments:

mo.stoneskin November 20, 2009 at 7:02 AM  

Beautiful. I come from an Oracle background, just getting started with Sql Server, and this is fantastic.

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep