One new feature in SQL Server Management Studio 2008 is the ability to restrict changes that would force a table to be re-created. This is a safety valve which can be turned off, if you choose. Let’s take a look…
In the previous release SSMS would allow certain table changes in the “Table Designer” while, transparently, data was unloaded to a temporary table, the table deleted then re-created by renaming the temporary table. As always, this powerful feature could be powerfully good or powerfully bad. Microsoft has restricted this option so that it doesn’t take you unawares. Obviously, with a large table, you wouldn’t want to make such a change accidentally.
Before doing any experimentation, it is always good practice to backup the database you are using, of course. You could create a classic backup or a database snapshot that can restored later.
To enter the Table Designer, you right-click on the table in question and choose “Design”. Any changes made here will be performed together when the Table is saved. SSMS will gather up the changes to be executed as a single transaction. If you try to close the Table Designer before saving, a save prompt will allow you to choose “No” to discard the changes. You can also trace SSMS (like any application) by running SQL Profiler in the background to see exactly what it is doing under the hood (I used to say “under the covers” until a proof reader advised me that this phrase was “inappropriate” – some things you never forget!).
Another nice feature of the Table Designer is the “Generate Change Script” button. If you have made changes, then the exact T-SQL code that is required to make those changes is generated. You can save the script, then discard changes and run the script at a later time.
The new feature in 2008 is stopping the re-creation of a table by default. I used the AdventureWorks2008 database and the HumanResources.Department table. In the Table Designer, I right-clicked the DepartmentID column and chose Remove Primary Key. A warning popped-up stating that this action would delete foreign-key relationships, and since I was experimenting, I clicked Yes. At this point the Generate Change Script button was available so pushing that showed the script necessary to remove the primary key. I could save the script for later by clicking Yes. I clicked the Save button and it told me that 2 tables were affected by this change. Clicking Yes, committed the change to those tables. This is an example of a change that does not require a table to be re-created so is allowed in SSMS 2008 by default.
I then proceeded to add a new column called Dept_Surrogate_Key with datatype int and set the identity property in the Column Properties window. This is a common situation when creating a Slowly Changing Dimension in a Data Warehouse and you need to track historical data changes (see a prior blog on this subject). I then made that new column the Primary Key by right-clicking and choosing Set Primary Key. Clicking the Generate Change Script or the Save button then showed that “Saving changes is not permitted. The changes you made require the following tables to be dropped and re-created”. This is the default behavior of SSMS 2008.
If you wish to override the default, (you can do this while the Table Designer is actually open during a change) you can go into Tools/Options/Designers/Table and Database Designers and clear the “Prevent saving changes that require table re-creation” checkbox. Then when you press the Generate Changes Script button, you will see the code necessary to drop and re-create the table while preserving the data. The code will also re-add any constraints and indexes to the new table. The code is executed successfully now when you save the table.
The same rules apply if you are making changes using the Database Diagrammer in SSMS 2008.
The new option is recommended to save you from yourself. But in reality, we should not be making changes directly using SSMS anyway. Far better to use a dedicated design tool like ER/Win. In production, changes are made methodically using pre-tested scripts and scheduled to minimize downtime.
But at least SSMS will help generate these powerful scripts for us.
Cheers
Brian
Recent Posts:
