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 wit开发者_开发技巧h 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.

Add exec sp_refreshsqlmodule 'TestFunction' before the second call.

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