Microsoft does a lot of things to try to help us not shoot ourselves in the foot. This little error is one of them. It’s an annoying error, and almost everyone turns it off automatically, so what is it and why is it there? Well, by default, when using the table designer, you cannot save the change if it requires dropping and re-creating the table. For example you have a table called DatabaseLog with several columns.

You go ahead and add a column in the middle

When you save you get the expected error (image shrunk a bit)

If you look at the code that SSMS is going to use (I’ll show you how below) then you’ll see that SSMS is going to create a new table, copy the data over, drop the old table and rename the new one.

Why do we care? Well, let’s say the table is pretty large, a couple hundred million rows taking up 100+gb of space. With the default process that’s going to take a long time to copy and you’re going to have enough space to make a second copy of that table. This can be a real problem in production. Not to mention if you have a laptop you have to take home at the end of the day.

Now, turning this error off is pretty easy. Just go into Tools -> Options then the designers tab.

Then just uncheck the Prevent saving changes that require table re-creation. Now you can save this type of change without seeing the error. That said, remember that it can be a problem if the table is of any real size. What I recommend is not just hitting save. Grab the code (remember I told you I’d show you how?) and run it yourself.

Go back into the designer. Now, if you right click in the white space and select Generate Change Script

you get a box with the code SSMS will use. While you are there you can also hit that little checkbox at the bottom so this window always comes up.

Copy this code out into a query window and you can run it separately. What’s the benefit? Control. You can make changes as needed, run it step by step, whatever. For a really big table, in an environment where you can’t afford much downtime, you could do the following:

1. Create the new table
2. Shut down the application
3. Rename swap the two tables
4. Restart the application

Your app had, what, a minute downtime? That’s usually acceptable if you pick the right time period (who wants to sleep anyway). Now you can start moving the data over from the old table a bit at a time. Move some rows to the new table, delete them from the old table, repeat. Minimal locking so minimal performance hit and you aren’t doubling up on many of the rows so minimal required extra space. And yes, you could generate this code yourself pretty easily. But letting SSMS generate it for you means never having to say Oops, I forgot that constraint or Oops, I forgot that security.

Regardless, there is a reason you see this error. Think twice before you turn it off and if you do be careful of those large tables.