One thing that really annoys me about SQL Management Studio is how it handles schema alterations.

There have been so many occasions where I have tried to change a table after its initial creation, for example to change something daft like an nvarchar(1024) to an nvarchar(MAX) only to be presented with a dialog informing me that…

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

If there is no data in the table that’s fine, still a little irritating, but fine. Just open up the script view, make any changes manually and hit execute. The story is a little different if there are already 10,000 rows of data in the table. I either risk corrupting the data by writing a SQL update or I can try to muddle through with the old type and tell my users that no, they can’t write a small novel in that text input field.

Today I discovered that there is actually a way to disable this behaviour, and in all fairness the save changes dialog does allude to this solution so I’m not entirely sure why I didn’t do this long ago. There is a setting in SQL Management Studio that, when disabled, will allow on-the-fly schema updates. You should be aware that it’s a little risky to disable this setting because it makes it very easy to make mistakes – such as changing a nvarchar(50) to a bit then hitting go is unlikely to end well for your data. Continue reading →