SQL tools/options prevent saving changes

What are possible side effects when using option to "开发者_开发百科Prevent saving changes that require table-recreation." Specifically, just adding a new field to table.

You are referring to SSMS. By default, you cannot save schema changes that involve a table recreation. Whenever I install SSMS, I immediately turn this option off.

Some schema changes require a temp table to be created, the data from the original table copied to it, a new table created, with the new schema, and then the data from the temp table copied to the new table. The temp table is then dropped. When this option selected, any schema change that requires this process is not permitted in SSMS.

IMO, there is no downside to turning this off, as long as you are aware that some schema changes require this, and, with a table with a large number of rows, the operation could take a long time.

Just adding a new column to a table is fine, provided you can accept that the new column will "appear" at "the end" of the table.

It's when people want to position the new column in a particular place in the list of columns that problems occur, because there's no such actual command to allow this to happen in SQL; So SSMS has to fake this by creating a new table, copying data across, deleting the old table, and renaming the new. All of these steps take time, during which it's unsafe for anyone to be trying to access this table.

Damien is right, just adding a column causes no side effects. This can easily be done using T-SQL, as this is one of the actions that is done without re-creating a table when using T-SQL. The other actions are:

• Modifying the NULL setting of an existing column
• Using RESEED for a column
• Changing data type of an existing column