views:

152

answers:

2

We have a scalar function that returns a DateTime. It performs a couple of quick table selects to get its return value. This function is already in use throughout the database - in default constraints, stored procs, etc. I would like to change the implementation of the function (to remove the table hits and make it more efficient) but apparently I can't do that while it is referenced by other objects in the database. Will I actually need to update or drop every object in the database that references it, update the function and then update or recreate all those objects to restore the reference to the function?
The function is being referenced by a handful of views, triggers, a couple of functions and a large number of default constraints and stored procs.

Thanks for any insight you can give.

The error I'm getting when I try to either Alter or Drop the function is:

Cannot [ALTER|DROP FUNCTION] 'dbo.GetClientCurrentTime' because it is being referenced by object 'DF_tbl_PatientOrder_Note_RecordCreated'.

+1  A: 

If the input parameters and output type are unchanged, you should be able to make any internal modifications you need to make without causing any trouble.

Ray
That's certainly what I thought. In fact, I should be able to make breaking changes as well - with the result being that I've broken it. But to simply not be able to change a function, period? That seems like a fairly ridiculous restriction. So I figure I just must be missing some hoop to jump through that would allow me to change or drop the function.
Casey C.
A: 

It depends. If the objects referencing the function have the WITH SCHEMABINDING option then you'll be explicitly prevented from messing with he function. Otherwise, the onyl restriction is ordinary DDL access locking restriction, meaning the plans in execution that use the function will place schema stability locks on the function and this will block your ALTER FUNCTION statements, as they require schema modification lock. But this would resolve itself when the plans finish execution.

Remus Rusanu
I've added the error I'm getting to the body of the question.I've looked up SCHEMABINDING in the docs and can't find a way to either determine whether it is on for an object or to turn it off for an object. Would you expect the error message to indicate the problem is related to SCHEMABINDING? Do you know of any way to turn it off?Thanks for your help.
Casey C.
What is `DF_tbl_PatientOrder_Note_RecordCreated`? Another function, a derived column, a check constraint?
Remus Rusanu
DF_tbl_PatientOrder_Note_RecordCreated is a default constraint.The function is only being referenced by a handful of views, triggers, a couple of functions and a large number of default constraints and stored procs.
Casey C.
That's a bummer then. Afaik you're going to have to drop the contraints, alter the function, then add the constraints back. Unlike CHECK constraints, the DEFAULT constraints cannot be disabled. Maybe somebody else has a smart trick how to circumvent this, but I don't know any.
Remus Rusanu
Yeah - I was starting to get that impression. At this point I'll probably just create a new function like "GetClientCurrentTime2" and slowly go through and replace references with the new function. Well thanks for your help.
Casey C.