缓存的函数列

发布于 2024-10-17 20:44:19 字数 702 浏览 5 评论 0原文

看起来 UDF 中的 select * 很危险。考虑这个脚本：

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

您将获得两个结果集，尽管我添加了 col3，但它们都具有相同的列数。如果重新创建表，并在中间插入额外的列，所有内容都会移动一列，显示错误列名下的数据。换句话说，列将保持不变，但数据有一个额外的列。

我无法找到有关此问题的任何信息，但在我看来，避免这种情况的唯一方法是始终在函数中指定列。

所以我的问题是，UDF 到底缓存什么？看起来输出列是——还有什么吗？另外，有什么办法仍然使用 select * 但可以防止这个问题吗？谢谢。

原文

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.

分享到QQ

分享到微博