Trevor Best (go**********@besty.org.uk) writes:
Is there a *simple* way to change a collumn from allowing null to not
null?
I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop FKs, copy data, drop
table, rename new table, rebuild FKs...
I'm saving a lot of these changes to run on another database at a later
date but would rather not require a terrabyte device to store the
script :-)
That is not the main problem with the scripts generated by Enterprise
Manager. The main problem is that if something goes wrong in the
middle of those scripts, you may end of with halfly-modified database,
and you may lose foreign keys forever. This is because the scripts
has a poor transaction scope.
Overall, I strongly discourage using the table designer in Enterprise
Manager to change tables. You can take the script as a starting point,
but there is a lot of problems to sort out. Some standard remedies
to apply:
o Remove all BEGIN and COMMIT TRANSACTION but the first BEGIN and last
COMMIT.
o Put all statements in EXEC('...'). (Except for calls to stored
procedures.
o Remove all GO.
o On all re-addition of foreign keys, insert WITH CHECK before
CHECK. (Yes, WITH CHECK CHCEK.)
For this particular case, adding a new column and then dropping the
old one may be a good idea, as David suggested.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp