User defined functiones compiled ?

  • I have lost the resource that answered this for me and can't seem to find it again ...

    I seem to remeber that one of the advantages of UDFs is that they are compiled once and then the compiled version can be executed many times, as opposed to sprocs that can be recompiled over and over ...

    I have a function that I need to call recursively and this info would be useful ...

    Unless of course there is a set based method to do multiple inserts into a table that would include an incrementing value

    cheers

    dbgeezer

  • UDFs AND SPs are both compiled.  Granted their are specific instances that force SPs to be recompiled but generally that is not the case.

    You will still need a sp to call your UDF (I think)...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Unless of course there is a set based method to do multiple inserts into a table that would include an incrementing value

    What exactly do you mean here?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I was kind of typing what I was thinking ...

    The reason I was using a function was I have to write a routine that will iterate over a table and 'fill' a related table with X number of rows that are determined by values in the first table.

    I can join the tables and get the max value of the sequence and then calculate the number of rows i need to insert but was thinking that i need a function to perform the inserts e.g.

    given

    id           number

    1             5

    2             9

    3            45

    4             6

    for each of the id's i have to perform the 'number' of inserts into another table, this is where i was going to use a function to perform a batch of inserts. i couldn't think of a set based solution to this problem whereas:

    select id, dbo.fn_fillrows(number)

    would work just fine. the original query was to find out if the function is compiled once and then the compiled version reused or if sql server would decide to recompile the function at any point during it's multiple executions.

    hope this clears thing up 😉

    cheers

    dbgeezer

  • hope this clears thing up 😉

    Sure, blame it on my language barrier

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • i think you were right - i didn't really explain myself at all

    cheers

    dbgeezer

  • You managed to confuse me as well.  I have no idea if this is helpful, but a generalized "Sequence" table can make a lot of "incrementing" processes set based.  Insert a bunch of rows once and then use the table over and over again.

    Create table [Sequence] ([Id] Int identity Not Null PRimary Key)

    go

    create proc fill_Sequence

        @MaxInt int

    as

     

    if @maxInt <= (select max(ID) from [Sequence])

     return

    While 1 = 1

     BEGIN

     Insert [Sequence] default values

     if @@Identity >= @MaxInt break

     END

     

    Signature is NULL

Viewing 7 posts - 1 through 6 (of 6 total)

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