Monday, October 25, 2010

SQL Server Managment Studio, Tools, Options: Why the 'Saving Changes Is Not Permitted' Error - Preventing Changes That Require Table Re-Creation Is A

Does a gun have a safety? Yes! And so should the Table back to the database world and away from the analogy:

Prevent saving changes that require the table to be re-created is a way to reduce the chance of huge tables being rebuilt: which fortunately is enabled by default. In some scenarios, one could save objects from being dropped completely (hmm, not anything I would admit to...choke, cough, guilty).  So, yes, I have to agree one hundred percent with Pinal Dave on his opinion, as well as Microsoft, which refers to this default option as a bug (you have to apologise for protecting data? My word, what has the world come to...).  In an Entreprise environment, as mentioned by Erland Sommarskog recently, to not have this option would be 'very dangerous to use on a production database.' 
Honestly, I would only allow DBAs who are fully versed with the risks of having a table recreated to disable the option, but that's just me - thus I am very glad this is a default option setting within Management Studio. I feel the best way to learn what is happening is to click on the script option and simply view the code (and cancel out of saving the changes as I usually do), select it all, then review it or modify it in the a T-SQL window.  We want to understand what is really happening in the code, and this is one way of forcing that to happen.

The Mixer, Microsoft Campus, Redmond, Washington

No comments:

Post a Comment