MS SQL Server, user defined function question

user defined function question

Author	Message
news #1 / 6	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

Nigel Rivet #2 / 6	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

Uri Diman #3 / 6	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

Erland Sommarsko #4 / 6	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

Vishal Parka #5 / 6	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. - Hide quoted text - - Show quoted text - Quote: >hope this made sense. >michael >.
Mon, 25 Jul 2005 17:40:55 GMT

AlfredXY #6 / 6	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

Page 1 of 1

[ 6 post ]