Thursday, May 29, 2008 3:36 PM
I have been playing with SQL Server 2008 "Katmai" for quite a while. Although the new management studio designer looks almost the same as that of SQL 2005, there're actually many new features under the hood, and some of them are quite thoughtful. I have just been discovering these goodies here and there everyday.
What I found today is also pretty interesting. By default, SQL Server 2008's designer no longer allows saving changes that require table re-creation. You will need to turn off this safety check manually if you enjoy the flexibility of saving table changes directly from the designer. This feature may be a little annoying for your day to day development work, but it definitely makes your data safer if you are working with tables with a huge amount of data.
To demonstrate this, we will try to add a new column in the Northwind.Suppliers table, and then attempt to save the change, as shown in the screen shot below.
You get immediate warning message saying the saving changes is not permitted. That makes sense. Adding a new column to a table obviously requires table re-creation. Now go to Tools --> Options --> Designers --> Table and Database designer. You should see a new option "Prevent saving changes that require table re-creation" is checked.
Simply uncheck this option if you still want to be able to save table changes from the designer.