user defined function question 
Author Message
 user defined function question
hi,

if i have a user defined function that simply returns a subset of a table
(ie a simple query), will it still have access to the indexes on the
original table?

i would like to include the results of the function in a second query
involving a join.  so what i am also asking is whether or not that join is
going to really struggle (because there is no index) or whether it is still
an efficient option - if not then i must join to the original table instead.

hope this made sense.
michael



Mon, 25 Jul 2005 16:59:03 GMT
 user defined function question

The udf will return a table variable which is a new object.
It doesn't have any connection to the tables or static data the udf used
to create it.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 25 Jul 2005 17:11:26 GMT
 user defined function question
news
You can see the differentiation between two query by yourself
Use set statistics io on
set statistics time on
set showplan_all on


Quote:
> hi,

> if i have a user defined function that simply returns a subset of a table
> (ie a simple query), will it still have access to the indexes on the
> original table?

> i would like to include the results of the function in a second query
> involving a join.  so what i am also asking is whether or not that join is
> going to really struggle (because there is no index) or whether it is
still
> an efficient option - if not then i must join to the original table
instead.

> hope this made sense.
> michael



Mon, 25 Jul 2005 17:16:07 GMT
 user defined function question
[posted and mailed, please reply in news]

Quote:

> if i have a user defined function that simply returns a subset of a table
> (ie a simple query), will it still have access to the indexes on the
> original table?

> i would like to include the results of the function in a second query
> involving a join.  so what i am also asking is whether or not that join
> is going to really struggle (because there is no index) or whether it is
> still an efficient option - if not then i must join to the original
> table instead.

I assume that your table is a table-valued function.

There is a distinct difference here between a multi-step function where
you return a table variable. This is equivalent that prior to the query
fill a temp table and then use that temp table in the query. (But the
table variable may be somewhat faster in this operation.)

On the other hand an inline function where you just say RETURN(SELECT-
statement) works a bit you like you ask for. That is, such a function
is basically a macro, and the query optimizer will consider the expanded
query as a whole.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp



Mon, 25 Jul 2005 17:36:28 GMT
 user defined function question
It will not use the index because if you are using UDF in
the from clause the result set is retrieved at the time of
execution of the function which is different from the base
table.

--Vishal.

Quote:
>-----Original Message-----
>hi,

>if i have a user defined function that simply returns a
subset of a table
>(ie a simple query), will it still have access to the
indexes on the
>original table?

>i would like to include the results of the function in a
second query
>involving a join.  so what i am also asking is whether or
not that join is
>going to really struggle (because there is no index) or
whether it is still
>an efficient option - if not then i must join to the

original table instead.

- Show quoted text -

Quote:

>hope this made sense.
>michael

>.



Mon, 25 Jul 2005 17:40:55 GMT
 user defined function question
Hi,

In design view, UDF makes the procedure more structural.

In performance view, if the resultset returned from UDF is
guaranteed to have few number of rows, it should have no
difference when joining a table in a query. But if
not, .... no index but table scan.

Regards,
AlfredXYZ

Quote:
>-----Original Message-----
>news
>You can see the differentiation between two query by
yourself
>Use set statistics io on
>set statistics time on
>set showplan_all on



>> hi,

>> if i have a user defined function that simply returns a
subset of a table
>> (ie a simple query), will it still have access to the
indexes on the
>> original table?

>> i would like to include the results of the function in
a second query
>> involving a join.  so what i am also asking is whether
or not that join is
>> going to really struggle (because there is no index) or
whether it is
>still
>> an efficient option - if not then i must join to the
original table
>instead.

>> hope this made sense.
>> michael

>.



Mon, 25 Jul 2005 17:42:19 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. A user defined function question

2. User Defined Function Question

3. User defined function question

4. User-defined function question

5. call a user defined function from another user-defined function

6. Question on user defined function (SQL Server 2000)

7. Question about user-defined functions

8. Newbie questions on User-defined functions in SQL server

9. Question on user defined function (SQL Server 2000)

10. user-defined function client server question

11. questions on user-defined function

12. more question about adding values from user defined function


 
Powered by phpBB® Forum Software