我们可以从函数而不是表变量返回临时表吗？

I have a function...in which I have return type as table variable... But performance will be increased if we use temp tables..as we have more data.

我有一个函数...其中我有返回类型作为表变量...但如果我们使用临时表,性能将会增加......因为我们有更多的数据。

4 个解决方案

#1

No, you cannot "return" a temp table - you can create that temp table before calling your function, and have your function write data into that temp table.

不,你不能“返回”临时表 - 你可以在调用函数之前创建临时表,并让你的函数将数据写入临时表。

But this has a tendency to get rather messy .... you need to make sure to have the temp table created before calling the function.....

但这有一种变得相当混乱的倾向....你需要确保在调用函数之前创建临时表.....

Have you really, honestly measured the performance of the two approaches?? I don't think temp tables are just always faster.... the approach with the table variable seems a lot cleaner and more intuitive to me.

你真的,老老实实地衡量了两种方法的表现吗?我不认为临时表只是总是更快......使用表变量的方法对我来说似乎更清晰,更直观。

#2

You cannot of course return the temp table. But you can define it in the calling stored procedure. Bear in mind this will only work for stored procedures. You cannot access temporary tables from within functions.

你当然不能返回临时表。但您可以在调用存储过程中定义它。请记住,这只适用于存储过程。您无法从函数中访问临时表。

#3

You can use CLR Table Valued functions that returns the actual table. CLR functions are functions you can write in your native language C#, VB etc. and the dll are embedded in the database.

您可以使用返回实际表的CLR表值函数。 CLR函数是您可以用您的母语C#,VB等编写的函数,并且dll嵌入在数据库中。

Learn more about it from here:

#4

this cover just about every way with pros/cons and sample code:

这涵盖了各种方式的优点/缺点和示例代码:

How to Share Data Between Stored Procedures by Erland Sommarskog

如何在Erland Sommarskog的存储过程之间共享数据

Using OUTPUT Parameters
Table-valued Functions
    Inline Functions
    Multi-statement Functions
Using a Table
    Sharing a Temp Table
    Process-keyed Table
    Global Temp Tables
INSERT-EXEC
Table Parameters and Table Types
Using the CLR
OPENQUERY
Using XML
Using Cursor Variables

#1