SQL 2008 + : Prevent saving changes that require table re-creation

Adding a new column for a clustered primary key (don’t ask..) I came up against this on my development box.

This is sensible. After all, adding a new clustered primary key is going to need the whole table to change, a potentially lengthy operation.

Generating the script for the operation proves this as it -

1. renames the existing table
2. creates the new one with the additional column
3. copies the data back

(and deals with removing and recreating indexes and constraints before and after respectively)

The option to control Management Studio’s behaviour is here -

Tools -> Options > Designer > ‘Prevent saving changes that require table re-creation’

ref; Brian Knight : Sql 2008 Designer Behaviour Change