Alter Table via Enterprise Manager

[arrowhouse]

Normally, I make table changes using alter table statements.

I am planning to make a change to a table via Enterprise Manager (EM) as I wish to control where the new columns appear in the table.

I have read that when you make changes via EM a copy is made of the table whilst the change is made.

Does anybody know if this is true?

I have two concerns, one the space that is needed for this & two the time it will take.

The table I want to do this to is aprox 21 million rows & 20GB.

Does anybody have any experiance/knowledge of making such changes?

I'm using 2000 for one server & SQL 7.0 for two others.

Thanks,

Liz.

 

[joebickley]

Hi as far as i am aware the EM doesnt make a copy of any tables. All the EM does is give you a visual to for writing alter/update etc etc statements. You dont see the code it creates.

Thats the way most of the snap ins for the management console work. Dont quote me on it but im pretty sure that is how it works.

 

[moonshadow]

Sorry Joebickley, you're wrong. SQL can create a copy of the table if the change requires it (e.g. adding a new field in the middle of the current ones) i.e. when it can't be done with a simple alter table statement. In these cases it tends to create the new table (with another name), transfer the data from the old table, drop it then rename the new one then add referential integrity etc. The good news is that you can ask it to save the change script (3rd button on left), so you can check it out first, or even schedule it to run out of hours.

 

[arrowhouse]

Ahh.. the magic third button. Me unaware as prefer old fashioned ALTER TABLE etc. Thanks - this confirms my concerns. The script goes:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_test_REARANGED_TABLE
(
REARANGED_TABLE_id, ...all the column names etc inc. the new ones in order required
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_test_REARANGED_TABLE ON
GO
IF EXISTS(SELECT * FROM dbo.test_REARANGED_TABLE)
EXEC('INSERT INTO dbo.Tmp_test_REARANGED_TABLE (REARANGED_TABLE_id,... etc )
SELECT REARANGED_TABLE_id,... etc FROM dbo.test_REARANGED_TABLE TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_test_REARANGED_TABLE OFF
GO
DROP TABLE dbo.test_REARANGED_TABLE
GO
EXECUTE sp_rename N'dbo.Tmp_test_REARANGED_TABLE', N'test_REARANGED_TABLE', 'OBJECT'
GO
COMMIT

Well that'll give the server a work out & max out the trasaction log...

So I think I'm using plan B which is to bcp query out with the new table format, trucate the table, alter it & then bcp back in. Joy!

Any other suggestions welcome.

Thanks for your time,

Liz.

 

[moonshadow]

Note also that the exec('insert... part of the script can fail if you have a table with a lot of long field names - SQL Server truncates the script at some point causing it to fail. Your idea seems the best, unless you want to add the fields at the end, and then create a view based on the table, with the fields as you desire. Why is it so important that the fields are in this order ?