Q: I went to change a column to a Primary Key with an Identity specification on a table in SSMS, but when I hit save I got an error message saying the table would have to be dropped then recreated. I’m ok with this. How can I set SSMS to allow drop and create in the SQL2014 table editor?
A: This is a common question. The fastiduous DBA would say “You shouldn’t have tried to do this in the GUI.” That’s not always practical, though. Developers working in their own development sandbox should have every opportunity to destroy their systems through experimentation, if only to learn how not to do things.
Here’s how you modify SSMS to allow you to drop and create tables when the editor suspects you might not actually want to do that (in the Table Editor).
First, a disclaimer:
Microsoft gives you this warning because it’s real. If the change you’re trying to make to your table is going to result in that table being dropped and recreated, then all of the data in that table is going to be deleted. Don’t delete all the data in your table if you didn’t mean to. Pay attention to warnings. They’re there for a reason.
In the SSMS menu, click on Tools, Options, and then find the Designers option in the tree on the left. Expand that and look for the Table and Database Designers option.
You’ll see that one of the checked options is “Prevent saving changes that require table re-creation.” Uncheck it, and hit OK. The next time you try to save a table that requires a drop and create, the editor will still warn you, but it will let you override the warning and proceed without further ado.