Re: Alter table via t-sql vs Ent Manager

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 15:14:17 +0530

Hi.

Difference:

If you do change in table structure using EM then:-

1. Unload the data
2. Drop the table and dependants (indexes)
3. Create the table with new definition
4. Create dependant objects
5. Loads back the data and delete the file.

If You do it Query Analyzer using ALTER TABLE then:-

1. It just directly changes the definition or adds a new column.

So it is allways advisible to change the table definition using Query
ANalyzer -- ALTER TABLE command. Since this activity is
logged we can revert back if the database is set in FULL recovery model.
As well as it is very fast, only disadvantage is using alter Table you cann
add a new column only at the last.

Thanks
Hari
MCDBA

"mid" <midbarsinai@midbarnospam.org> wrote in message
news:1ar8ck4gwbl0l.dlg@midbarnospam.org...
> Hello all!
>
> What is the difference between running a alter table statement that adds a
> new column to a sql server 2000 table using Query Analyser or Enterprise
> Manager?
>
> I noticed that if we do it using Ent. Manager it creates a big script that
> copies the whole table to a temp table, adds the new column and than
> renames it to the original table dropping the old one.
>
> If we do it using QA we just need a "ALTER TABLE X ADD ..."
>
> Question IS:
> Is EM preferable, more reliable?
> Is QA safe in the case of a server failure while running the query. What
is
> the fastest/reliable method if I want to add a column to a 13 milion
record
> table?. BOL states that alter table statements are logged a fully
> recoverable. This recovery would be automatic or would require any manual
> statements?
>
> Very confused as you see...
>
> TIA
> mid

Relevant Pages

  • Re: Add constraint returns syntax error on constrain clause
    ... Using Northwind, if you try, in the query designer: ... CurrentProject.Connection.Execute "ALTER TABLE Products ADD CONSTRAINT ...
    (microsoft.public.access.queries)
  • Re: Embedded Queries?
    ... alter table MyTable ... Columnist, SQL Server Professional ... the time of running the query. ... UPDATE chrisslu SET 'discquantity' = '(SELECT ...
    (comp.databases.ms-sqlserver)
  • Re: Alter table via t-sql vs Ent Manager
    ... My database is in Simple recovery model. ... Supose I am running a long alter ... > logged we can revert back if the database is set in FULL recovery model. ... >> new column to a sql server 2000 table using Query Analyser or Enterprise ...
    (microsoft.public.sqlserver.server)
  • Re: Warning: Existing columns have ANSI_PADDING off
    ... Try adding the column by running an ALTER TABLE query in Query ... Analyzer instead of using Enterprise Manager: ... ALTER TABLE MattsTable ADD newColumn CHAR ...
    (microsoft.public.sqlserver.programming)
  • Re: Excluding columns from select
    ... That depends on the product:) If a table has had ALTER TABLE ... query, but compiled code will have expanded the * ... let me push a button and expand the * to a list I can see for production ... constraints, Declarative Referential Integrity, datatypes, etc. in your ...
    (microsoft.public.sqlserver.programming)