Menu
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'.
734
You should never use a scalar function in a where clause as indexes can't be used and frequently a full table scan is required. And if you need to force the CTE part of the query to execute first then you can materialise the results into a temp table and then select from that.
You must own the function to use ALTER FUNCTION . To change a function's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the function's schema.
Scalar-valued functions can be executed by using the EXECUTE statement. If you EXECUTE a function rather than use it in a SELECT statement or constraint, you can leave out the schema name in the function name, and it will look in the dbo schema followed by the users default schema.
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 only 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.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
