The best made plans. Oh well, it is our busiest time of the year…
FYI, you may want to be careful when using two user defined functions in TSQL that return tables and doing a join. You may think that they were going to be processed like any other join; however, in a recent assignment I tried to simplify and extend a complex query by creating a single table (from a complex join) within a UDF and joining it with another UDF (created from another complex join). What happened was quite concerning. The query went from executing in 2 seconds to taking well over 4 minutes.
After examining the execution plan, it appears that SQL was executing the second UDF once per each row generated by the first UDF. This resulted in a memory table with over 400 million records (OUCH). I might have expected this if I were attempting a cross join; however, it was specified as a left join. I changed the procedure to create a temp table and insert the rows returned from the second UDF and then joined against the temp table and it works as expected.
So, since I did not need the UDF’s for re-use, I find that it was much faster to create two temp tables and join these rather than using the UDFs. As it turned out, this was even faster than using a separate query as the source of the first.
I am not sure why this is occurring, but I am sure that SQL server cannot assume that you are not passing a parameter that may change based on the results of the initial UDF.
Comment if you want to see an example of this...