Getting Bit by Forgetting

Published 20 May 08 09:53 AM | Buck Woody 

I was working on a SQL Server 2005 system yesterday and I had really awful performance on one of my queries. I just couldn't figure out why it was running slow. I decided to use the new SQL Server 2008 T-SQL Debugger on the code, and sure enough - I found the problem. I had a User-Defined Function (UDF) being referenced in the code, and when I stepped through it, I noticed that it didn't have the "WITH SCHEMABINDING" option in it. The effect of that is that SQL Server 2005 treats a UDF without that option as non-deterministic, which means it won't evaluate it properly until later in the cycle.

All that to say two things: use WITH SCHEMABINDING in your UDF's (more here) and the Debugger is cool!

Filed under: , ,

Comments

# Saggi Neumann said on May 20, 2008 5:10 PM:

Hey,

I remember that UDFs in SQL 2000 cannot be non-deterministic, and that UDFs in 2005 can be non-deterministic (i.e they can reference getdate(), and not do a dirty trick with a view).

However, if I remember correctly, a hotfix that disables the non-deterministic option came out because of performance issues (post sp2).

Am I making any sense?

Anyway, the link you provided doesn't mention using WITH SCHEMABINDING in order to mark the UDF deterministic, unless it is obvious that WITH SCHEMABINDING doesn't allow calling functions such as getdate(), etc.

Cheers,

S. Neumann

# Buck Woody said on May 22, 2008 12:26 PM:

Good points all - you should use the WITH SCHEMABINDING as I mentioned, and hte explanation for when you want to use that and when you don't should be in that link.

Thanks for posting!

Anonymous comments are disabled

About Buck Woody

http://www.buckwoody.com
Page view tracker