mzak (7/22/2010)
Some interesting results!I first tested the suggested hypothesis of declaring/setting the pi/180 as a constant in the T-SQL udf.
Average time = 1444.4 milliseconds
Then I tested using no declared variables within the T-SQL udf.
Average time = 1456.3 milliseconds
Finally, I tested no udf call at all (inline expression).
Average time = 664.4 milliseconds!
Of course, the inline expression would be a nightmare to maintain so there are some tradeoffs.
Now that is something that one fights with in his head. I am an application developer (GIS and Web based) and I must admit my raw SQL skills have not been keeping up. My head is more stuck in the .Net Object Oriented world and because of this I tend to want to keep things clean and readable and create reuse. That was one of my main reasons for suggesting the variable to hold the PI()/ 180 value.......for reuse.
The second reason is I seriously thought that caculating once and referencing the variable over again would create a performance improvement. In my tests I did not see any and in fact the variable declare was slightly worse but minimal. In the slightly worse but minimal scenario, I would opt for readability over performance. In the case you just showed above, doing everything inline, I would have to go the other way and as a developer this bugs the hell out of me lol.
EDIT: Just want to clarify - I am not saying that you can't keep things clean/readable/reusable in SQL.....the fact that it is more top down vs object oriented makes it a less cleaner/readable by nature. Because of this I find myself not caring so much about readability as I do about performance at this tier.