custom functions and query optimization

I am building some functions and have not found documentation telling me
if/how that are optimized. For example:
SELECT
companyName,
dbo.getAddress(companyID),
dbo.getContact(companyID)
FROM company
Will the query optimizer build a plan rolling the function SQL statements
into the main SQL statement?
Or will it perform seperate optimization plans for the functions? If this
is the case will I get better performance by not using functions?
Thanks.Hi,
If you are doing set based operations (selects) then you are better off not
using a functions, it will be quicker
Functions have differnet compile plans then the queries they are used in.
But if you are doing complex procedure type functions then you have no
option but to use them if you didn't want to use a Stored Procedure
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"astro" <astro@.bcmn.com> wrote in message
news:hMljf.201$f41.86@.tornado.rdc-kc.rr.com...
> I am building some functions and have not found documentation telling me
> if/how that are optimized. For example:
> SELECT
> companyName,
> dbo.getAddress(companyID),
> dbo.getContact(companyID)
> FROM company
>
> Will the query optimizer build a plan rolling the function SQL statements
> into the main SQL statement?
> Or will it perform seperate optimization plans for the functions? If this
> is the case will I get better performance by not using functions?
> Thanks.
>|||On Wed, 30 Nov 2005 17:59:41 GMT, astro wrote:

>I am building some functions and have not found documentation telling me
>if/how that are optimized. For example:
>SELECT
>companyName,
>dbo.getAddress(companyID),
>dbo.getContact(companyID)
>FROM company
>
>Will the query optimizer build a plan rolling the function SQL statements
>into the main SQL statement?
Hi astro,
Depends on the kind of user-defined function. There are three kinds of
user-defined functions: scalar functions, inline table-valued functions
and multi-statement table-valued functions. See CREATE FUNCTION in Books
Online for the details.
For inline table-valued functions, SQL Server will "roll the function
statements into the main statement", as you describe it. For multi-line
table-valued functions and scalar functions, it won't.
The function calls in the example above appear to be scalar, so in this
specific case, the answewr would be "no".

> will I get better performance by not using functions?
Quite probably. But the only way to know for sure is to test both
versions.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks both of you for your replies.
"astro" <astro@.bcmn.com> wrote in message
news:hMljf.201$f41.86@.tornado.rdc-kc.rr.com...
> I am building some functions and have not found documentation telling me
> if/how that are optimized. For example:
> SELECT
> companyName,
> dbo.getAddress(companyID),
> dbo.getContact(companyID)
> FROM company
>
> Will the query optimizer build a plan rolling the function SQL statements
> into the main SQL statement?
> Or will it perform seperate optimization plans for the functions? If this
> is the case will I get better performance by not using functions?
> Thanks.
>