Last post Dec 18, 2007 07:43 PM by ndinakar
Dec 11, 2007 12:32 PM|PureWeen|LINK
For example if I make a column not null the SQL Script generated.
Removes all indexes, keys, etc...
than creates a temp table
Than moves the data into the temp table
then deletes the old table
renames the temp table
Recreate all the indexes, keys, etc...
Is there a structure reason for it? Like now that the DB is being alerted that the column is not null it wants to re insert all the data so it can re-organize it?
Because I can just write my own script to the effect of
ALTER [TABLE] [COLUMN] PARAM TYPE NOT NULL
one line of script that seemingly does the exact same thing..
So I'm just curious when I change a column to non null why doesn't it just generate a simple one liner?
Dec 18, 2007 05:24 PM|david wendelken|LINK
Actually, your command will fail unless you have already updated the null values to non-null values.
That said, I think the answer is: "because the programmers doing the job either ran short of time or were short of vision"
Dec 18, 2007 05:29 PM|PureWeen|LINK
well yea but the SQL Generated by the Management studio will fail as well if I haven't updated the null values to non-null values as well..
So that's not really an argument for distinction
Dec 18, 2007 07:43 PM|ndinakar|LINK
(1) To maintain Data integrity/consistency and (2) if you cancel out anytime in between it doesnt have to do a whole buch of ROLLBACK's. There is some rollback that's done though.
The other reason is, it has to do a lot of validation to make sure its not breaking or violating any existing constraints. So it will re-validate the constraints against all the data.