Thursday, June 15, 2017

Editing table structure in SQL

By default, SSMS will not let you insert a column in the middle of a table and save the changes.  This is because it requires dropping and recreating the table.

However, if you are OK with doing that, you don;t need to do it manually.  You can just shut off this validation in SSMS.

Tools > Options > Designers > Table and Database Designers
Clear the Prevent saving changes that require the table to be re-created check box.

It's that easy.


Actually, here are all the changes what this validation catches:

  • Change settings allow nulls of a column.
  • Rearrange the columns of the table.
  • Change the data type of the column.
  • Add a new column.
As for why this validation is there, it seems to mostly have to do with change tracking.
https://support.microsoft.com/en-us/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-changes-is-not-permitted

No comments:

Post a Comment

SQL 2022 TSQL snapshot backups!

  SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...