Change one of the column data type on a large table.

• sqlfavor

  Valued Member

  Points: 65

  More actions

  September 20, 2010 at 1:20 pm

  #224190

  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