October 26, 2007 at 12:19 pm
I have a few developers working on creating functions.
What are the best practices on when and where to use them.
Can you use it in the From, Where, Select clause or are there places you shouldn't use function?
One of the developers mentioned that they had created a function, but was using it in the wrong place and it caused a performance hit.
Thanks in advance for your help
October 26, 2007 at 12:34 pm
It is really hard to be hard and fast on this as reusability of tasks within a statement may make them a very good choice especially for reusability. I consider them often myself when doing a common task but weigh in the performance hit (especially as the data set grows) to rule them out.
October 26, 2007 at 12:36 pm
Table valued stored procedures do not generate statistics. This means if you are going to join one function to another or anything else along these lines, performance won't just be bad. It will be catastrophic. Also be cautious when using scalar functions. These can more or less act like cursors within the query.
Most of the time a stored procedure does what you need. Functions are for special circumstances, repeatability of some particular piece of code, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2007 at 5:51 am
In most cases, sing a function within a join or a where clause will force table scans.
Because SQL needs to resolve the function before it can determine if a record meets the criteria, it will need to go through every row in the table.
I tend to only use them in the select list when it can really simplify something that is not going to perform well anyway. I usually try to avoid use of functions in a where clause.
Every situation is different, don't make blanket rules. If you have a specific situation, post it and we may be able to help you understand the pros and cons for your particualr case.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply