Article on UDF concerns

I just read an article on UDF problems (see URL below). The author contends that UDFs can cause as many problems as a CURSOR and be just as bad. A UDF doesn't seem like a bad piece of code, but it just so happens, at my current workplace, we are having more problems with poorly designed UDFs than we do with CURSORS.

Imagine a production DB with an Access front end program that is used by thousands of data entry people. Now imagine that it takes a 3 second query over 2 minutes to actually run. Multiple that by the # of users... Our boss is pretty steamed about the number of UDFs that everyone (not just the DBAs) seem to be designing to get out of the hassle of rewriting queries in code. After reading this article, it's no wonder.

Of course, the one of people who has commented in response to the article makes a good point to refute the issue to. I guess it just depends on how good a coder you are.

Article can be found at:

http://www.windowsitpro.com/SQLServer/Article/ArticleID/39036/39036.html

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[NoCoolHandle]

Definintly a good read for anyone that is concidering UDF's.

They definitly have their place, but they have enough power to allow someone good with TSQL to do stuff that just shouldn't be done!

Knowledge of why it can be good, and why it can be bad will hopefully allow more intellegent design.

It is like everthing. Used wrong you can have problmes. Too many indexes not only slows things down but takes up heaps of disk space. Pick the wrong clustering key and you have even more issues. (AND INDEXES ARE SEEN AS GOOD!)

What is needed before using any of SQL's tools (incuding instead of triggers, indexed views, federated servers etc) is a thorough understanding of what the implications of your actions are.

READ READ READ. AND Question everyting. Books all have errors. TEst for yourself and be aware that as you add thousands and millions of rows that the way your actions impact performance is lilkey to change. Understanding exactly how you data is stored and how SQL works is critical.


The Bible in my mind is Kalen Delaney's "Inside SQL Server" Ms Press.

A must read if you don't want to build your own preformance problmes into your Database design.

If you read SQL Mag you will know she writes very good articles, but the book is the only one I know of it's elk that is 99.999% correct. I have know Kalen since the days of 6.5, but she was part of Sybase before MS took SQL over and has been one of the most knowledgeable sources I have found. (Short of one or two folks that work high in the SQL team and often they defer to her. David Campbell is another great source of GOOD knowledge on SQL. Everything I have read of his is 1. Understandable and 2.Accurate. (But heck he is one of the guy's that helped to build it.

My 1c (ok a couple more and slightly off topic)

Rob