Re: Enterprise Mgr - alter table fails
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 13 Mar 2006 23:19:05 +0000 (UTC)
(stephanie.harrell@xxxxxxxxxxxxx) writes:
Here is the most common example. The developer needs to change the
length of a field in a table. They use Enterprise Mgr, right click on
the table and select "design". They make their change and when they
hit save, they get the following error. (There is no data in the
table.)
'prob1' table
- Unable to create index 'PK_prob1'.
ODBC error: MicrosoftODBC SQL Server DriverSQL ServerTable 'prob1'
already has a primary key defined on it.
MicrosoftODBC SQL Server DriverSQL ServerCould not create constraint.
See previous errors.
I can not just tell them to switch over and use Query Analyzer. Some
of these people have little or no SQL knowledge and would never make it
through alter table statements. They build tables for their front page
apps and that's as far as they get into SQL.
To be very blunt, if they can't write change scripts with ALTER TABLE,
they should not be changing tables at all. Performing changes to tables
is an advanced operation, and requires careful understanding, particularly
if you are to do it in a production environment.
The Design Table function in EM has several serious flaws (and all these
flaws are carried over to SQL 2005). I include a list below, from a post
that I made just the other day.
I was not able to reproduce the problem with the table you posted. But
you could get an idea of what's going by requesting getting a change
script from EM. (Which you should do anyway, because of all the bugs
in the Table Designer.) You find this function on the 3rd button from
the left in the toolbar.
Here is a list of know defenciencies with the Table Designer:
1) The transaction scope is wacko. What should be one transaction is
split up into several, which can lead to a table change being only
partly implemented, and your database becomes a mess. Or you just lose
constraints, without knowing it.
2) In the generated script, the transaction spans multiple batches. This
means that if one batch fails with a batch-aborting error, the
transaction is rolled back. The remaining batches in the script are
still carried out which is not likely what you want. (This flaw does
not appear if you save directly, as the Table Designer does not continue
to submit batches if there is an error.)
3) The table designer essentially behaves as there has been no later
version of SQL Server. That is, in many cases where it could use
ALTER TABLE, it instead runs a script where it creates a new table
and copies data over.
4) Say that you have to tables A and B. B has an FK to A. You first make a
change to B, and generate a script. Then you change your mind, and
close without saving anything. Instead you make a change A. When you
generate script (or even worse just press Save), you find that the
change to B is included.
5) All constraints that are reapplied, are reapplied WITH NOCHECK,
which means that they are not trusted as far as the optimizer is
concerned. This have serious impact on performance, not the least
with partitioned views.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: Enterprise Mgr - alter table fails
- From: stephanie . harrell
- Re: Enterprise Mgr - alter table fails
- References:
- Enterprise Mgr - alter table fails
- From: stephanie . harrell
- Enterprise Mgr - alter table fails
- Prev by Date: Re: How to type a multiline text in a column?
- Next by Date: Re: SQL Enterprise Manager
- Previous by thread: Re: Enterprise Mgr - alter table fails
- Next by thread: Re: Enterprise Mgr - alter table fails
- Index(es):
Relevant Pages
|
