Make Functions Schema-bound

Create user defined functions with the SCHEMABINDING clause where possible.  In the absence of this clause, SQL Server must assume at compile time that statements using the function may bind to a function which accesses and updates data.  Note that even if the UDFs do not access data or perform any updates, the SQL Server optimizer must be prepared for this in case the statement at run-time binds to a different UDF which does access and update data.  This leads the optimizer to add halloween protection that is often unneccessary.  This protection often manifests as Spool or Sort iterators in the plans and can dramatically slow down plan performance.  Making UDFs schema-bound is necessary even if the UDF is uniquely named, and even if no UDFs access or update data. 

This behavior has changed in SQL Server 2005, and applications that are porting from SQL Server 2000 to SQL Server 2005 should re-examine whether their UDFs can be schema bound and to make them schema bound if they are not already so marked.

Background on Halloween Protection.

"Halloween protection" in database systems refers to a solution for a problem that can occur in update queries. The problem occurs when an update itself affects the rows selected for update.  For example, imagine a company wants to give every employee a 10% pay increase. If the update query is unlucky enough to walk the salary index then the same row may be selected, updated, the update moves the row ahead in the index, and then the row will be redundantly selected again and again.  This problem is corrected by isolating the rows chosen from the effects of the update itself.  For example, a SPOOL operation which stores all the rows to be updated outside of the context and any index can provide the necessary isolation. SORTs are also sufficient for isolation purposes.