Skip to Content
avatar image
Former Member

Table Function Query Plan / statistics io

Greetings,

I am testing table functions. Ex:

CREATE FUNCTION fn_teste2(@key int)
returns table ...

as return select ...

So far so good, i can use the function and join with a table. But when i try to get the plan and io cost, nothing is returned for the function. Ex:

set statistics io, time on
set showplan on
go

.....

TABLE FUNCTION Operator
EXECUTE TABLE FUNCTION
fn_teste2

it just prints that. No infos about the io cost too.

The table I am joining with the function shows plan and iocost, but nothing inside the table function.

In procedures, plan and cost works. Why not in functions? Is there some way to get plan and io cost of table functions?

Regards,

FINAL EDIT: It was a bug on my sql client. Running on Interactive SQL all works fine.

EDIT: if i run the function alone,

select * from fn_teste2(123905)

plan and io cost prints as usual - the problem (not prints) happens when I join with other tables.

EDIT 2: full example (plan and io show only sysobjects)

CREATE OR REPLACE FUNCTION fn_syscolumns(@obj_id int)
returns table (obj_id int, colname longsysname)
as
return select id, name from syscolumns where id = @obj_id
GO

set statistics io, time on
set showplan on
GO

select t1.id, t2.colname
from sysobjects t1
join dbo.fn_syscolumns(t1.id) t2 on t1.id = t2.obj_id
where
t1.id = 1
GO

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers