473,322 Members | 1,188 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

user defined funcyions in sql server 2000

hi,

it might be a very stupid question but i want to know whether an
execution plan is created in user defined functions in sql server 2000
like stored procedures.

Thanks.

Oct 14 '06 #1
4 1739
On 14 Oct 2006 05:27:33 -0700, shark wrote:
>hi,

it might be a very stupid question but i want to know whether an
execution plan is created in user defined functions in sql server 2000
like stored procedures.
Hi shark,

Not a stupid question at all, and not a simple answer either. That's
becuase it depends on the type of function.

* Scalar function.
Has an execution plan. Note that the function is executed once for each
row in the calling query's result set, so the execution plan will be
executed many times.

* Multistatement table-valued function.
Has an execution plan. The function is called once per query in which it
is used and the results are materialized in a temporary table that is
then used in the calling query.

* Inline table-valued function.
Has NO execution plan. If a query refers to an inline TVF, the name of
that function is replaced with the definition before the calling query
is optimized (just as with a view). This allows the optimizer to pull
tricks that would otherwise be impossible. But the result is, of course,
that an execution plan is built for the calling query, not for the
function itself.

--
Hugo Kornelis, SQL Server MVP
Oct 14 '06 #2

Hugo Kornelis wrote:
On 14 Oct 2006 05:27:33 -0700, shark wrote:
hi,

it might be a very stupid question but i want to know whether an
execution plan is created in user defined functions in sql server 2000
like stored procedures.

Hi shark,

Not a stupid question at all, and not a simple answer either. That's
becuase it depends on the type of function.

* Scalar function.
Has an execution plan. Note that the function is executed once for each
row in the calling query's result set, so the execution plan will be
executed many times.

* Multistatement table-valued function.
Has an execution plan. The function is called once per query in which it
is used and the results are materialized in a temporary table that is
then used in the calling query.

* Inline table-valued function.
Has NO execution plan. If a query refers to an inline TVF, the name of
that function is replaced with the definition before the calling query
is optimized (just as with a view). This allows the optimizer to pull
tricks that would otherwise be impossible. But the result is, of course,
that an execution plan is built for the calling query, not for the
function itself.

--
Hugo Kornelis, SQL Server MVP
Thanks Hugo . but then what id functiond do have an execution plan wht
are the advantages of using them over stored procedures.

Oct 15 '06 #3
On 15 Oct 2006 11:37:32 -0700, shark wrote:

(snip)
>Thanks Hugo . but then what id functiond do have an execution plan wht
are the advantages of using them over stored procedures.
Hi Shark,

User-defined functions and stored procedures are used in so different
situations and have such different properties that the choice which one
to use is made based upon how it will be used, not whether an execution
plan gets generated.

I don't know of any situations where you could easily change a stored
procedure to a function or vice versa.

--
Hugo Kornelis, SQL Server MVP
Oct 15 '06 #4
Hi, shark

As Hugo said, the choice to use SP-s or UDF-s should be made based upon
how it will be used and what it should do. My rules are the following:
1. If it must change data, then use a SP, else proceed to step 2;
2. If it requires parameters, then use a UDF, else use a view.

Razvan

Oct 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Tom Dauria | last post by:
I have a SQL database with an Access front end. In the database I have a read only and a read write role. When a read only user opens the database I want all the fields on the form to be locked...
2
by: Steve D | last post by:
I've looked all over but can't find a solid answer. I've got a function that runs from a View and when the function runs the first time it is calculating a Temperature for a group of Formulas. ...
2
by: shivprasad koirala | last post by:
hi all(happy raksha bandhan day) we have one of Automation software for sales running for a customer.He was cool for the first month of product, but later popped with adding some extra...
2
by: Bruce | last post by:
How can you tell which datatypes in a given database are user defined (with a query, not by looking in Enterprise Manager)? This is for SQL Server 2000.
1
by: B-BoB | last post by:
Hi All, Would someone give me an explanation on how SQL 2000 server backs up a user database? Is SQL server make a copy of user database first, and then backup it up to a defined backup device?...
13
by: dawatson833 | last post by:
I have several stored procedures with parameters that are defined with user defined data types. The time it takes to run the procedures can take 10 - 50 seconds depending on the procedure. If I...
3
by: jhmosow | last post by:
How can I restore the original User Defined Function Template? I made an error and saved one of my UDFs as a template but would like the original template restored? TIA
4
by: serge | last post by:
http://www.csharphelp.com/archives2/archive342.html I am using the sample code from this link but I am unable to figure out how to retrieve the list of the User-Defined Functions. I am able to...
2
by: royaldothighness | last post by:
Hi all, I defined unsigned_int in my database, which uses unsigned_int_range rule. The unsigned_int_range rule is defined as follows: @unsigned_int >=0 and @unsigned_int <=4294967295 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.