CODEWITHSUNDEEP
sql-servercachinguser-defined-functions
Nelson Rothermel
Nelson Rothermel

Reputation: 9736

Function columns cached

It looks like select * in a UDF is dangerous. Consider this script:

create table TestTable (col1 int, col2 varchar(1))
insert into TestTable values (123, 'a')
go

create function TestFunction
(
    @param1 bit
)
returns table
as
return
(
    select * from TestTable
)
go

select * from TestFunction(0)

alter table TestTable
add col3 varchar(1)

select * from TestFunction(0)

drop function TestFunction
drop table TestTable
go

You will get two result sets, both with the same number of columns, even though I added col3. If the table is recreated an an extra column is inserted in the middle, everything will shift one column over, showing the data under the wrong column name. In other words, the columns will stay the same, but the data has an extra column.

I wasn't able to find any information about this, but it seems to me the only way to avoid this is to always specify your columns in a function.

So my question is, what exactly does a UDF cache? It seems output columns are--anything else? Also, any way to still use select * but prevent this problem? Thanks.

Upvotes: 0

Views: 295

Answers (2)

JeffO
JeffO

Reputation: 8043

The function's metadata does not automatically update. Run an ALTER statement.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453067

Add exec sp_refreshsqlmodule 'TestFunction' before the second call.

Upvotes: 2

Related Questions