Skip to Content
0

Table Function Query Plan / statistics io

Jun 22, 2017 at 10:25 PM

83

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded

It might be of some help if you can provide a small reproducible example, eg, a version of your function that references system tables, plus a sample query that joins the function with some system tables (by using system tables we don't have to worry about creating/popluating test tables).

0
Former Member
Mark A Parsons

Hi Mark,

I edited (edit2) with a small repro, please apologize me for not posting before!

Thanks.

Best wishes,

0
Former Member
Mark A Parsons

Mark,

It was a bug in my sql client.

Running on Interactive SQL all works fine.

Sorry about raising this false alarm.

Thanks for the attention,

Best wishes,

0

No problem. Glad you figured it out.

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers