Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
A query that references a nondeterministic user-defined function may run slower in SQL Server 2005 than in SQL Server 2000
| Article ID | : | 952217 |
| Last Review | : | May 8, 2008 |
| Revision | : | 1.1 |
SYMPTOMS
In Microsoft SQL Server 2005, a query that references a nondeterministic user-defined function may run slower than the same query runs in SQL Server 2000.
Note Any user-defined function that is not created by using the WITH SCHEMABINDING clause is always treated as nondeterministic.
Back to the top
CAUSE
Sometimes, a user-defined function is called to project additional columns from a base table. If the base table has any joins or any filters that reduce the number of rows that are returned, SQL Server 2005 postpones the call to the user-defined function until after the join operations or after the filter operations in the execution plan. This mechanism reduces the number of times that the user-defined function is called. Therefore, this mechanism improves performance.
However, the projected column may be used for a join. Or, the user-defined function may be used in a filter that is more selective than any other predicates. In these cases, for a nondeterministic user-defined function, it is a better execution plan to call the user-defined function before the join operations or before the filter operations. For a deterministic user-defined function, SQL Server 2005 can safely postpone the call to the user-defined function in the execution plan.
Back to the top
RESOLUTION
To resolve this problem, create the user-defined function by using the WITH SCHEMABINDING clause.
Back to the top
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top
MORE INFORMATION
To determine whether a user-defined function is a nondeterministic user-defined function, run the following query.
SELECT OBJECTPROPERTY(object_id('<FunctionName>'), 'IsDeterministic') If the query returns 0, the user-defined function is a nondeterministic user-defined function.
Back to the top
APPLIES TO
| • | Microsoft SQL Server 2005 Standard Edition |
| • | Microsoft SQL Server 2005 Developer Edition |
| • | Microsoft SQL Server 2005 Enterprise Edition |
| • | Microsoft SQL Server 2005 Standard X64 Edition |
| • | Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems |
| • | Microsoft SQL Server 2005 Enterprise X64 Edition |
| • | Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems |
Back to the top
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please