Thursday, 10 April 2014

Alter Table Not Permitted

By David Postlethwaite

At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some articles on Gethyn’s blog about the bits I didn’t manage to cover.

image

You may have come across this error message when using SQL Server Management Studio

It’s new in SSMS 2008.
You cannot, by default, make changes to a table that will require it to be dropped and re-created.
These changes are
Changing data type on existing columns
Or changing allow nulls on existing columns
Or changing order of columns

But you can turn this behaviour off
Tools Menu -> Options -> Designers
Untick "prevent saving changes that require table re-creation"

But beware.
If it is a table with large amount of data it could take some time to copy it from the old to the new table