I always went under the assumptions that while you had to be very careful when using user defined functions, very simple ones would have little or no performance impact in general. I did a test today that showed this to be wrong. I wanted to write a function that strips out the domain from the user name and returns it. The logic is:right( suser_name(), len(suser_name()) - charindex('', suser_name())) I did a test and updated all rows in a table with 450,000 records. When I used the expression in the UPDATE, I saw a +100% peformance improvment over using the function. The function only contains one line and its the same as above. Does this make sense? I don't understand why there would be such a huge difference. The execution plans are virtually the same, except the function has a table spool which is 0% of the execution plan. So what's going on here?
If you include the expression in the UPDATE query, then SQL knows it only has to evaluate suser_name() one time for all the rows that will be updated. With the UDF, SQL has to evaluate suser_name() for each row all over again. Don't waste server time and resources!