User defined functiones compiled ?

Question

User defined functiones compiled ?

SteveB-74825

Hall of Fame

Points: 3290
More actions
March 30, 2005 at 4:31 am

#185576

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

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

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]

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 3

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 5

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

cheers

dbgeezer

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

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