Add column from development database table to prod

问题:

I got an issue When some table on the development database has more columns added, and right now I should add the column in the production database.

I try to add the column manually, by Add Column on Table Tree, but when I try to save, it show me a warning:

here's the complete warning message :

Saving Definition Changes to tables with large amounts of data could take a 
 considerable amount of time. While changes are being saved, 
 table data will not be accessible.

is this safe? or is there another method to do this ?

UPDATE : I Tried to click Yes but after some minutes it give me another warning that said Time Out, and the process canceled, and yes the data in the production table is totally huge

回答1:

Basically this means that the SQL server is going to go over all the records in the table (on disk) and change them. This is an operation that will take a lot of IO and will lock your table to reading / writing.

Something else to consider is your DB timeout as mentioned in http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d46b158-a089-48c9-8153-53c5ed116d37/sql-2005-updating-table-definition-with-large-amounts-of-data-timeout

This is relatively safe (except for the lock issue) and will be the case in any method you will use (maybe without the nice UI warning).

回答2:

To fix it, go to the Tools Menu and select Options then under Designers select Table and Database Designers the first option allows you to disable the timeout, or make it greater than the default 30 seconds.

Ref: http://surf11.com/entry/197/saving-table-times-out-in-sql-server-200

回答3:

There is two options for set time-out-execution time

1. Tools > option > Designers > Transaction time-out after

Set bigger value than 30 sec. (max 65536) this will fix that issue

but there is one more option

2. Tools > option > Query Execution > Execution time-out

Generaly this is not couse a problem because Default value is 0 (unlimited) already but check this and fix it to 0 if you see different value there.

It is completely safe and there is no possibilty to data corrupt.