Post reply

Why DML operations are not supported in functions?

  • September 23, 2011 at 9:22 pm

    #257904

    can anybody explain me this?

  • September 23, 2011 at 9:35 pm

    #1386081

    Nope, sorry, they just aren't.. And I wouldn't expect that to change anytime soon..

    CEWII

  • September 23, 2011 at 10:14 pm

    #1386082

    That's why sps exist!

  • September 24, 2011 at 3:14 am

    #1386093

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2011 at 5:50 am

    #1386096

    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

    #1386102

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 24, 2011 at 10:47 am

    #1386115

    I like Gila's answer..

    CEWII

  • September 11, 2014 at 7:19 am

    #1744749

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply