SQL Server 2008 - Preventing Database Changes

I was laying out a database for an application today with SQL Server 2008. Now this is my first encounter with this latest release so I am expecting everything to work as it has in the past. I thought I would share a quick note on a new piece of functionality, that I was surprised by. I am doing a quick dump on what happened and do not claim to have in-depth knowledge of this latest release. So I created my tables using the Database Diagram so I could throw them together quickly and build all my relationships. Well that was done in no time and everything was created as expected. It was when I started down the path to apply a tweak that I ran into something I never expected. After making a change specifying a column as an Identity column, I click the X to close the screen and choose to save the change. I was promptly greeted by the following message.

My first thought is, "NO SQL Server you are wrong, I will cancel and hit Save again to show you". Why, because I didn't read the message. Well SQL Server showed me and let me know that I hit cancel, see below.


Unfortunately I was not returned to the table designer because I had selected Close. Thankfully that was low impact for me since I only made a single modification, but if I had made several I would have been irritated. Ok, so I go back into the designer and save. Ahhh, SQL Server is protecting me from breaking something, nice. This is definitely a good feature on a production database to keep the riff raff from bringing things to a halt. I see a Save Text File button and I bet that will output a create script with my change so I can apply it safely. I click the button, Yippee, save the file and open it up. What would you expect to happen? Well if you thought like me, you would be wrong. Check out what was output.


What is that? Joy! Joy! this is great, I get text file on my system that is completely useless. Now granted there may be something where you can set up a script to be output, this is my first roll in the hay with SQL Server 2008, but would that not be on by default. If this is all the Save Text File button does though, I would ask why have it. How is that capability useful to me if there is a restriction on saving my change to the database and I cannot find a way around it. If you are aware of something I am missing, please share.

Well, I knew I didn't want to be hindered by this any longer so I found the setting to turn off this functionality. I suspect this will be a common complaint from people who install SQL Server 2008 the first time. So to change it navigate through the Tools - Options menu and select the Designers entry on the left. Then simply uncheck the "Prevent saving changes that require table re-creation" setting.

Now you can work as you have in the past. Saving all those little modifications that could potentially break something. Again, I think the setting is a good idea to have on a production system. I have seen people apply changes to production without care and cause problems across several applications. If you are working on your development system it will be a pain and you probably just want to turn it off. Very quickly, here is the link to this functionality on SQL Server Books OnLine.