August 2, 2011 at 3:04 am
We are running SQL2005.
We have a production table that contains a large amount of data (100's of GB's). We need to add some new columns to it, remove some columns from it and make some varchar columns larger.
What would be the best way to do this so as not to cause SQL Server to duplicate the table behind the scenes as a means of applying the changes? We don't have the disk space for SQL Server to do that nor the time.
I know that new columns can be added to the end and made NULLable which is very quick. Will adding a NULLable column between existing columns be just as quick? (Not required and don't plan on doing it this way but asking the question in case I get asked.)
My real concern is removing columns and increasing varchar sizes (e.g. varchar(30) to varchar(50)).
August 2, 2011 at 6:07 am
Roger Sabin (8/2/2011)
I know that new columns can be added to the end and made NULLable which is very quick. Will adding a NULLable column between existing columns be just as quick?My real concern is removing columns and increasing varchar sizes (e.g. varchar(30) to varchar(50)).
here's a test table
create table test.test
(col1 int, col2 int, col3 int)
after using the GUI to add a column to the middle of the table, here is what it does (by right clicking and selecting generate script)
BEGIN TRANSACTION
GO
CREATE TABLE test.Tmp_test
(
col1 int NULL,
col2 int NULL,
[col2.5] int NULL,
col3 int NULL
) ON [PRIMARY]
GO
ALTER TABLE test.Tmp_test SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM test.test)
EXEC('INSERT INTO test.Tmp_test (col1, col2, col3)
SELECT col1, col2, col3 FROM test.test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE test.test
GO
EXECUTE sp_rename N'test.Tmp_test', N'test', 'OBJECT'
GO
COMMIT
It creates a temp table, inserts from the old, drops the old, and finally renames the new table. lots of work.
To just add a column to the end is much more simple.
BEGIN TRANSACTION
GO
ALTER TABLE test.test ADD
col4 int NULL
GO
ALTER TABLE test.test SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
To change the length of a varchar column, it does the same thing as the first example. The table is first duplicated with the change, inserted into, old table is dropped, and new table renamed. I made [col3] a varchar(25) then changed it to 50 to generate the script.
BEGIN TRANSACTION
GO
CREATE TABLE test.Tmp_test
(
col1 int NULL,
col2 int NULL,
col3 varchar(50) NULL
) ON [PRIMARY]
GO
ALTER TABLE test.Tmp_test SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM test.test)
EXEC('INSERT INTO test.Tmp_test (col1, col2, col3)
SELECT col1, col2, col3 FROM test.test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE test.test
GO
EXECUTE sp_rename N'test.Tmp_test', N'test', 'OBJECT'
GO
COMMIT
So for both adding a new field and changing an existing one, it is less work (for the database) to just add it to the end.
August 2, 2011 at 6:10 am
Oh yeah, and keep an eye out for those table locks where they popup. If you're modifying a table with millions (and millions!) of records, you might be impacting end users.
Of course, do your testing and all that disclaimer junk.
August 2, 2011 at 6:17 am
calvo (8/2/2011)
ALTER TABLE test.Tmp_test SET (LOCK_ESCALATION = TABLE)
Why? Table is the default for lock escalation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2011 at 6:57 am
GilaMonster (8/2/2011)
calvo (8/2/2011)
ALTER TABLE test.Tmp_test SET (LOCK_ESCALATION = TABLE)Why? Table is the default for lock escalation.
That's what the GUI generated as a script, and I pointed the locks out to make sure they weren't missed.
I also removed the following for brevity.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
August 2, 2011 at 7:36 am
Roger Sabin (8/2/2011)
We have a production table that contains a large amount of data (100's of GB's). We need to add some new columns to it, remove some columns from it and make some varchar columns larger.I know that new columns can be added to the end and made NULLable which is very quick. Will adding a NULLable column between existing columns be just as quick? (Not required and don't plan on doing it this way but asking the question in case I get asked.)
My real concern is removing columns and increasing varchar sizes (e.g. varchar(30) to varchar(50)).
New columns should be added at the end of the table, adding columns in between existing columns equates to recreate the table following the new desired schema.
Removing a.k.a. dropping columns is a metadata operation meaning that it's is immediate and does not affects the size of the table or particular rows. If you want to reclaim the space a table reorg is needed.
Increasing the size of a varchar() column is also a metadata operation, nothing to worry about.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 11:12 am
Thanks for all the replies.
You are confirming what I already thought was true - adding a column in the middle of the table would end up duplicating the table. Not something that we can afford to do.
About removing columns, I just thought we would leave the columns in the table and rename them to something that would indicate "DO NOT USE".
For increasing varchar sizes, one reply says that this will duplicate the table. Another one says it won't. Is there any way to prove which happens? And, the same for removing columns - will doing so not duplicate the table. Any way to prove that?
August 2, 2011 at 11:21 am
Depends how you do it. Through Managment Studio's GUI it'll probably end up recreating the table. Rather use the appropriate T-SQL ALTER TABLE statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2011 at 11:22 am
Don't use the GUI.
It'll never create another table unless you code it.
As for DO NOT USE, I preffer just dropping the column. Not 100% sure about the impact on this one tho.
August 2, 2011 at 11:24 am
Mine, where I said it would duplicate, is because I was doing it through the GUI. When you make changes through the GUI, it produces a script that gets executed to make the change.
This is definitely not the same thing as running a script (alter table [tablename] alter column [column] <datatype>).
So I can't say for sure what happens exactly when the script gets run.
August 2, 2011 at 11:40 am
Let me make sure I have this.
If I use ALTER TABLE to change the varchar size (e.g. varchar(20) to varchar(50)), SQL Server will not duplicate the table but instead just update the metadata. I like that.
If I use ALTER TABLE to remove a column, will that also only update the metadata and for a duplication on the table?
August 2, 2011 at 11:46 am
The duplication is CODED within SSMS GUI.
Don't use the GUI, use a script and you'll see exactly what the server will do.
August 2, 2011 at 11:54 am
Roger Sabin (8/2/2011)
Let me make sure I have this.If I use ALTER TABLE to change the varchar size (e.g. varchar(20) to varchar(50)), SQL Server will not duplicate the table but instead just update the metadata. I like that.
If I use ALTER TABLE to remove a column, will that also only update the metadata and for a duplication on the table?
Yes and Yes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 11:57 am
PaulB-TheOneAndOnly (8/2/2011)
Roger Sabin (8/2/2011)
Let me make sure I have this.If I use ALTER TABLE to change the varchar size (e.g. varchar(20) to varchar(50)), SQL Server will not duplicate the table but instead just update the metadata. I like that.
If I use ALTER TABLE to remove a column, will that also only update the metadata and for a duplication on the table?
Yes and Yes.
Yes Paul, I know. 😉
August 2, 2011 at 12:00 pm
Roger Sabin (8/2/2011)
Let me make sure I have this.If I use ALTER TABLE to change the varchar size (e.g. varchar(20) to varchar(50)), SQL Server will not duplicate the table but instead just update the metadata. I like that.
If I use ALTER TABLE to remove a column, will that also only update the metadata and for a duplication on the table?
Just don't use the Management Studio table designer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply