4

I want to rename a column in a table. I see two approaches

  1. Use sp_rename() and modify stored procedures to refer to new name.

  2. Create new column, copy data from old column to new column, modify stored procedure etc. to refer to new column, eventually drop old column.

We can't use #1 as renaming column might leave stored procedures broken and we cannot afford any downtime.
If we go with #2, there is possibility that both old and new columns would co-exist for sometime after the data is copied over from old to new column but before the stored procedures deployed to use the new column.

Is there any way to keep the new column in sync with any updates/insert/deletes done to old column?

  1. Can the AFTER triggers help here? But triggers usually increase the transaction time, so may not be a favorable solution.
  2. Can I replicate data between two columns of the same table?
  3. Any other possible solutions?

Also does sp_rename() cleanly updates all the references to the column - like stored procedures, functions, indexes etc?

HappyTown
  • 6,036
  • 8
  • 38
  • 51
  • Does sp_rename() cleanly updates all the references to the column? - Seems like not, [Renaming a column in MS SQL Server 2005](http://stackoverflow.com/questions/1255903/renaming-a-column-in-ms-sql-server-2005) – HappyTown Jan 19 '17 at 17:13
  • 2
    No, sp_rename does not change any of the objects referring the column -- but installing procedures should only take few seconds. I don't really see how you could do it faster... and how are you maintaining your database if you can't do that? – James Z Jan 19 '17 at 17:17
  • 1
    Might there be any applications affected? Any dynamic SQL floating around? – HABO Jan 19 '17 at 17:38
  • @JamesZ We mostly deploy in multiple steps and ensure backward compatibility between two steps this way 1) customers don't notice any downtime due to deploys 2) we can rollback easily and only the last change 3) if any issues surface in existing features, we can hold off further deploys. That means we cannot guarantee that we will be able to deploy modified procedures in few seconds. – HappyTown Jan 19 '17 at 17:53
  • To me it sounds that you're just making it too complex by trying to have multiple steps. If you just rename column + install procedures as one step, then everything should work, and if it doesn't, then rename back + install old versions -- a lot simpler than trying to come up with some kind of data copy / replication. – James Z Jan 19 '17 at 18:25
  • Replacing the table with a view with the same + extra column should probably work, but still, that's again something more that could break – James Z Jan 19 '17 at 18:32

2 Answers2

9

First confirm there are no references to the column from outside the database like application code directly querying the column without going through stored procedures.

Here is how I renamed the column without causing the downtime -

  1. Add a new column besides the existing column. The new column has same data type as the old column but the new name.Also create indexes, modify replication etc. on the new column based on the use cases.
  2. Modify all stored procedures writing (insert/update operations) to the old column to also insert/update in the new column.
  3. Copy over the data from old column to the new column for the existing records. Step 2 and 3, together, now ensure that the new column will remain in sync with the old column.
  4. Modify all stored procedures reading from the old column to now read from the new column.

Now that all code has transitioned to use new column, we need to clean up -

  1. Modify stored procedures from earlier Step#2 to stop referring to old column.
  2. Drop the old column.
HappyTown
  • 6,036
  • 8
  • 38
  • 51
2

You could rename your table and create a view using the old table name and have the view include an alias for your column.

SQLPrompt by redgate has a feature called Smart Rename which can rename a column and update all of the references to the new name.

From SQL Prompt 7 documentation:

SQL Prompt can create a script that allows you to rename objects in your database without breaking dependencies. You can rename the following:

  • Tables (including columns)

  • Views (including columns)

  • Stored procedures (including parameters)

  • Functions (including parameters)

When an object is renamed:

  • SQL Prompt also modifies any objects that reference, or are referenced by, the renamed object to ensure that dependency links are not broken.

If you have previously renamed an object using SQL Server Management Studio or Enterprise Manager Rename, or the T-SQL sp_rename command, the object definition will contain the original name.

Any objects that reference this original name are not updated.

To help you locate objects that reference objects that no longer exist, see Finding invalid objects.

  • The original permissions and extended properties of the object are preserved.
Community
  • 1
  • 1
SqlZim
  • 37,248
  • 6
  • 41
  • 59