Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
SO I have been reading about WITH SCHEMABINDING and how it can improve the performance of queries using a scalar UDF by omitting the table spool operator from the execution plan. I think I understand halloween protection.
My question is: If I add WITH SCHEMABINDING to a UDF used in a SP is it possible that a SP does not give the same results? If yes in what scenario?
In fact it's the other way around. If you check the Best Practices section of the CREATE FUNCTION doc, you'll see that:
If a user-defined function is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. We recommend that you implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:
- Specify the WITH SCHEMABINDING clause when you are creating the function. This ensures that the objects referenced in the function
definition cannot be modified unless the function is also modified.
