[SOLVED] Forcing Management Studio to use ALTER TABLE instead of DROP/CREATE

Forcing Management Studio to use ALTER TABLE instead of DROP/CREATE

I'm wondering if is there a way to force MSSQL Management Studio to produce a script like this:

ALTER TABLE Mytable
ADD MyCol bit NOT NULL
CONSTRAINT MyColDefault
DEFAULT 0 WITH VALUES


ALTER TABLE [dbo].Mytable
ALTER COLUMN MyCol2 int NULL

GO

when I alter a very simple property of a column on a table. If I do this in the designer and ask for the produced script, the script doesn't do such simple tasks, but instead copies all the data in a tmp table, drops the original table, renames the tmp table with the original table name. And, of course, drops and recreates every constraint and relationships.

Is there any option I can change to change this behaviour? Or, this may be possible, is there some danger I don't see in using the simple ALTER TABLE above?

thanks.

Solution

You can't change the behaviour, it's just that the default script it creates is not always the most efficient. It creates scripts in a format it knows will work, although frequently the results will be slow and resource-heavy. I recommend you get used to creating the script for all changes yourself as you can better optimize it. There's nothing wrong with what you've created (providing you have no existing constraints/dependencies on MyCol2 that would be invalidated by it becoming a nullable int)