Home > SQL server DBA administration > Change the date type for big size existing table for Production server

Change the date type for big size existing table for Production server


I have tried all option like to take the database in single user mode, Tried to change the data type by TSQL , Override the table designer timeout value to indefinite number  but nothing has been workout. The Microsoft KB article solution is also not workout for the tables which has lots of data.

 

The correct workaround to change the data type for big tables:

 

  1. Create a temporary table with the same table schema.
  2. Copy all the data from the actual table to the temporary table.
  3. Truncate all the data from the actual table.
  4. Change the data type in actual table
  5. Copy all the data from the temporary table to the actual table.

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: