September 21, 2010 at 7:07 am
I am re-posting http://www.sqlservercentral.com/Forums/Topic989733-146-1.aspx for forum correctness
I need to change data type of a column from TINYINT to SMALLINT. Table has 11 columns of fixed integer data types (int, smallints, tinyint) together 80 bytes. One single column is nvarchar(300) which is typically contains 200 bytes(100 unicode chars). The column being changed does not have any index. The table is partitioned, fairly big about 500 million rows.
Option 1) I can simply issue ALTER Table statement and change column data type. However, I need to make it an online operation where underlying table is still available for DML. I believe ALTER Table will acquire schema lock and table will be unavailable for DML. Also, this option will grow the log quite a bit.
Option 2) Add new nullable column with desired data type, update the data from old column to new column in batch mode. Drop old column and rename the new column with old column name. It will be slow and long operation but will allow online table DML.
The questions I have are, with option 2 what's the impact on internal structures of table when new column is added to the end and another is dropped. Does this cause page splitting, physical fragmentation, wasted space? Will option 2 increase size of the table as compared to option 1 due to empty space caused by dropping old column.
Would appreciate your opinions.
AKM
September 21, 2010 at 7:14 am
I suggest you read this post by Kalen Delaney. Based on it I'd say Alter Column is the way to go.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 8:24 am
I agree with Jack.
But to talk about option 2 for a minute (becuase there will be times you have to go that way), simply adding the nullable column will not impact pages within the existing table. Only when you start moving data into that column will you potentially run into issues. And yes, some of those issues may be page splits, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2010 at 9:13 am
Thank you gentlemen. Let me go through Kalen's article and I will come back with more questions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply