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?

migrated from stackoverflow.com Apr 14 '12 at 19:53

This question came from our site for professional and enthusiast programmers.

  • 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ć Mar 22 '12 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 Mar 22 '12 at 11:46
  • @marc_s Well it's worth a shot, lots of smart people around here :) – robertc Mar 22 '12 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 Mar 22 '12 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) – Martin Smith Mar 22 '12 at 14:11

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.

  • That sounds like more work and the same level of risk as removing and re-adding the columns. – robertc May 27 '12 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 May 28 '12 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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