September 23, 2011 at 9:22 pm
can anybody explain me this?
September 23, 2011 at 9:35 pm
Nope, sorry, they just aren't.. And I wouldn't expect that to change anytime soon..
CEWII
September 23, 2011 at 10:14 pm
That's why sps exist!
September 24, 2011 at 3:14 am
Because a function may not have side effects (change the permanent database structure).
If a function doesn't have side effect, the optimiser is free to run it as many times as makes sense for a query plan. If it had side effects (eg creating a temp table) then if the optimiser chose a different plan the results would be different and that is not permitted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2011 at 5:50 am
why they are not allowing functions to make changes in data of the tables?
is it like functions are designed for some logical output only?
September 24, 2011 at 8:36 am
As I said, if a function doesn't have side effect (DML orDDL), the optimiser is free to run it as many times as makes sense for a query plan. If it had side effects (eg creating a temp table or inserting a row) then if the optimiser chose a different plan the results would be different and that is not permitted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2011 at 10:47 am
I like Gila's answer..
CEWII
September 11, 2014 at 7:19 am
GilaMonster (9/24/2011)
As I said, if a function doesn't have side effect (DML orDDL), the optimiser is free to run it as many times as makes sense for a query plan. If it had side effects (eg creating a temp table or inserting a row) then if the optimiser chose a different plan the results would be different and that is not permitted.
Hi Gail,
I was going through your post, Could you explain this reason more clearly as to Why DML operations are not allowed within Functions.
I can think of a condition, where My SP is calling a function. And lets say function allows DML.
So , optimiser has to calculate a plan for SP and Function. But it is not possible to Execute an SP with a separate plan of SP and a separate plan of Function.
What are your views on this ?
GilaMonster (9/24/2011)
As I said, if a function doesn't have side effect (DML orDDL), the optimiser is free to run it as many times as makes sense for a query plan. If it had side effects (eg creating a temp table or inserting a row) then if the optimiser chose a different plan the results would be different and that is not permitted.
What it means that different plan creates different result and that is not permitted.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy