Scalar function used in calculated column, what's the cleanest way to update the function?

Question

I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences. I wondered if there was a cleaner way of doing it?

Answer 1

This may be more work but it should get (approximately) what you are after:

1 - Create a new table that has just your PK from the main table and the updated function.

2 - Drop your old column

3 - Rename your old table to something like TableName_Base

4 - Create a VIEW that joins your main table to your lookup table, with the fields in the order that you want to see them. Name the view whatever your original table name was.

This will potentially create some issues with INSERTS and UPDATES though since you'll be accessing via a view. To deal with that you can either insert into the table directly, bypassing the view.