Alter table via t-sql vs Ent Manager

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
midHi.
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|||Thanks Hari.
My database is in Simple recovery model. Supose I am running a long alter
table statement via QA and sql server stops. When I bring SQL online again
will the structure of the table reflect the old structure? Will I have a
corrupted table?
When you say "we can revert back if the database is set in FULL recovery
model" I think you mean that if needed we can have the old structure back
again by aplying the last Transaction Log backup. Am I correct?
Thanks again,
mid
On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
[vbcol=seagreen]
> 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 can
n
> add a new column only at the last.
> Thanks
> Hari
> MCDBA
>
>
>
> "mid" <midbarsinai@.midbarnospam.org> wrote in message
> news:1ar8ck4gwbl0l.dlg@.midbarnospam.org...
> is
> record|||Hi,
Restarting while doing a ALTER TABLE (Any DDL statement) is bit risky. The
table might not get corrupted rather the change
will be rolled back once the SQL Server comes back. So you can get the old
image of table.
This scenario will be same for SIMPLE or FULL recovery model. In FULL
recovery model even if the ALTER TABLE succeedes you can roll back
to old shape.
But doing this is very risky.
Thanks
Hari
MCDBA
"mid" <midbarsinai@.midbarnospam.org> wrote in message
news:3ye70mka23ys$.dlg@.midbarnospam.org...[vbcol=seagreen]
> Thanks Hari.
> My database is in Simple recovery model. Supose I am running a long alter
> table statement via QA and sql server stops. When I bring SQL online again
> will the structure of the table reflect the old structure? Will I have a
> corrupted table?
> When you say "we can revert back if the database is set in FULL recovery
> model" I think you mean that if needed we can have the old structure back
> again by aplying the last Transaction Log backup. Am I correct?
> Thanks again,
> mid
> On Thu, 20 May 2004 15:14:17 +0530, Hari wrote:
>
cann[vbcol=seagreen]
adds a[vbcol=seagreen]
Enterprise[vbcol=seagreen]
that[vbcol=seagreen]
What[vbcol=seagreen]
manual[vbcol=seagreen]