Nilas Jensen's Library tagged t-sql →  View Popular

Improving query plans with the SCHEMABINDING option on T-SQL UDFs

This blog describes how the SCHEMABINDING option specified during creation of T-SQL UDFs may affect query plans involving these UDFs in SQL Server 2005. Armed with this knowledge, you may find that you can dramatically improvement your query performance for free.

Before I jump into the details, let me briefly summarize the take-away: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.