10

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?

8
  • 4
    I resolved this only partially by creating dummy functions calling real ones. Of course, if signature changes, then it is all over again.
    – Nikola Markovinović
    Commented Mar 22, 2012 at 11:41
  • 3
    Unfortunately, I don't think there's any "magic" or "cleaner" way of doing this. If you need to modify the function - you'll need to drop the columns, modify the function, and re-add all columns. No way around this.
    – marc_s
    Commented Mar 22, 2012 at 11:46
  • @marc_s Well it's worth a shot, lots of smart people around here :)
    – robertc
    Commented Mar 22, 2012 at 11:50
  • Agree with the above comments. Thought there might have been scope for SYNONYMs to offer some help here (reference a SYNONYM in the computed column) - but you can't ALTER a synonym once created to "redirect" it to another object.
    – AdaTheDev
    Commented Mar 22, 2012 at 12:08
  • 1
    Still nowhere near as painful as updating the definition of a CLR UDT (even when the change is just a tweak to a method and doesn't affect the storage representation at all) Commented Mar 22, 2012 at 14:11

1 Answer 1

2

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.

2
  • That sounds like more work and the same level of risk as removing and re-adding the columns.
    – robertc
    Commented May 27, 2012 at 20:44
  • 1
    @robertc It'll be less disruptive if this is a high transaction environment, but yes it's still pretty messy.
    – JNK
    Commented May 28, 2012 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.