Wednesday, June 20, 2007

SQL - Adding columns to a table

I'm a great proponent of using Enterprise Manager for creating and altering tables. For years I used the GUI exclusively for database management and only used the query analyzer for writing queries and doing quick selects.
In my last projects, I had to create and alter tables using scripts. Since the site was live and I was working on the staging site, the database schema on the live site would have to be altered instantaneously. I looked up how to alter tables using scripts and found something that I could have used on many occasions when altering table in Enterprise Manager.
Enterprise Manager is a great tool, but it has one shortcoming when dealing with tables. If you need to add a column with a default value, you would have to run an update query on the table because most of the time, the column would get added with nulls instead of the default value. If you need to add a column with a not null constraint, that's when it gets fun. You need to the column with a default and not null checked off, save the table, update the table, check not null on and save the table one more time. Annoying, time consuming, not fun. Now lets tackle the same problem using a script:
ALTER TABLE table-name ADD
column-name column-type NOT NULL DEFAULT default-value WITH VALUES
Short and beautiful. DEFAULT specifies a default value and WITH VALUES instructs SQL Server to populate all rows with the default value.

No comments:

Post a Comment