|
|
Posted by Erland Sommarskog on 06/05/05 00:44
Neil (nospam@nospam.net) writes:
> Thanks. It would be nice if EM provided a way to automatically script that
> for you, the way it does for adding entire tables. But I guess some things
> need to be done by hand.
Count yourself lucky for not having found it, and damn the day David
told you that you could do it.
To wit, yes, there is a function for this in Enterprise Manager, but, no,
you should not use it. There are several serious flaws with it, and you
could end up messing up your database.
As David mentioned, EM will recreate the table if you insert a column in
the middle. What he didn't say is that EM will do this also if you change
or drop a column, despite there is an ALTER TABLE syntax for this.
Basically, EM assumes that it's talking to an 6.5 database.
Now, recreating table is necessarily not bad. In fact, this can be
preferable over an ALTER TABLE in some situations, even if ALTER TABLE
is available. But recreating the table, requires you to have some sort
of recovery strategy, if the operation fails half-way. EM applies a
transaction scope, and as long as you run the thing directly from EM,
it's safe, but if you save the script, the script is not safe. Furthermore,
EM's transaction scope is wacko.
Another horror story with EM is that you can start doing a change to
table, then abandon that table and close it. Yet, you may find this
change performed anyway when you save, because this table was referred
by some other table you saved!
So don't use Enterprise Manager to modify tables. The same applies to
the Modify Table function in the new SQL Server Management Studio.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|