在SQL Server函数中不使用临时表的原因 [英] Reason for Temporary table are not used in sql server function

问题描述

大家好，



我知道我们不能在SQL服务器功能中使用临时表。它是什么原因？



我知道我们不能在SQL服务器功能中使用事务管理。是什么原因。


我搜索了这么多博客。我仍然没有找到任何答案。请帮忙。

Hi All,

i know we cannot use temporary table in SQL server function. what is the reason for it?

i know we cannot use transaction management in SQL server function. what is the reason for it.

i searched so many blog. still i didn't find any answer for my question. Please any help on this.

推荐答案

我不知道您提出这个问题的理由。但我想你需要这样的东西。您可以尝试使用表值变量： https://msdn.microsoft.com/en-us/library/bb510489 .aspx [ ^ ]



[注意]

根据定义，函数不应改变它的环境（与程序相反，不应该返回任何东西） - 和tempdb是函数体范围之外的东西。这是一种方法。由于tempdb是一个数据库，并且在同时运行之间并没有真正隔离表，所以它也是一致的问题。



另请参阅： https://www.simple-talk.com/sql/t-sql-programming / temporary-tables-in-sql-server / [ ^ ]

I don't know your reasons why you have asked this question. But I suppose you needed something like this. You could try table valued variable: https://msdn.microsoft.com/en-us/library/bb510489.aspx[^]

[Note]
By definition a function should not alter it's environment (in contrast to a procedure, that should not return anything) - and tempdb is something that's outside the function's body scope. That's one approach. As tempdb is a database, and tables are not really isolated between simultaneous runs, it really is a matter of consistency also.

See also: https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/[^]

最后，正如Mehdi所说，这是由创建SQL Server的人做出的决定。你可以抛出他们的愿望，但不能保证他们会接受他们。



我只能猜测决定背后的原因，但我认为它可能与确保函数调用期间的数据库状态一致。一个事实是函数可以在select语句中使用。另一件事是select语句可能不会以任何方式改变数据库状态。这意味着该函数可能不会创建一个情况，其中另一个查询的结果将根据函数调用而不同。



如果说使用临时表可能会导致数据库的查询结果可能会有所不同，具体取决于函数是否被调用，换句话说，如果函数已经修改了临时表中的数据。



但好处是你可以随时使用表变量，参见 DECLARE @local_variable（Transact-SQL） [ ^ ]

In the end, as Mehdi said, it's a decision made by the people creating the SQL Server. You can throw them wishes but there's no guarantee they will accept them.

I can only guess the reasons behind the decision but I'd think that it can be related to ensure consistent database state during the function call. One fact is that a function can be used in a select statement. Another thing is that the select statement may not alter the database state in any way. This means that the function may not create a situation where a result of another query would be different depending on the function call.

Having that said using temporary tables could cause a situation where the query results from the database could differ depending if the function is called or not, in other words if the function has modified the data in the temp table.

But the good thing is that you can always use table variables, see DECLARE @local_variable (Transact-SQL)[^]

这篇关于在SQL Server函数中不使用临时表的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！