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 ADDShort and beautiful. DEFAULT specifies a default value and WITH VALUES instructs SQL Server to populate all rows with the default value.
column-name column-type NOT NULL DEFAULT default-value WITH VALUES