Years ago, omeone asked a question that is pretty much exactly my question:
http://www.code400.com/forum/forum/i...-deterministic
Birgitta answered the question, but I'm not sure if I completely understand the answer about how long the cached answer is used... her answer almost seems to imply that it's forever until the parameter changes in the call.
I have an SQL UDF that accepts one parameter StoreNumber and uses Current_Date within the function to figure out the rolling 12-month sales for that store. So this week I might call the function with SALES12(123) to get the last 12 months' sales for store 123. Next week, if I make that exact same call for store 123, will I still get the same answer? How long is the cached answer kept until the function finally actually goes out and figures out a new answer? I don't mind getting the same answer back all day long, but I can't get the same answer back days and weeks later. But setting the function to NOT DETERMINISTIC seems to have a noticeable negative impact on performance.
Your thoughts?
http://www.code400.com/forum/forum/i...-deterministic
Birgitta answered the question, but I'm not sure if I completely understand the answer about how long the cached answer is used... her answer almost seems to imply that it's forever until the parameter changes in the call.
I have an SQL UDF that accepts one parameter StoreNumber and uses Current_Date within the function to figure out the rolling 12-month sales for that store. So this week I might call the function with SALES12(123) to get the last 12 months' sales for store 123. Next week, if I make that exact same call for store 123, will I still get the same answer? How long is the cached answer kept until the function finally actually goes out and figures out a new answer? I don't mind getting the same answer back all day long, but I can't get the same answer back days and weeks later. But setting the function to NOT DETERMINISTIC seems to have a noticeable negative impact on performance.
Your thoughts?
Comment