Click here to Skip to main content
15,899,754 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Comments
Mehdi Gholam 27-Sep-15 1:55am    
Design decisions by the sql team...
kalisiddayya 27-Sep-15 2:18am    
Without any disadvantage they cannot limit the temporary table. Just i want to know the disadvantages.

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/[^]
 
Share this answer
 
v2
Comments
Mehdi Gholam 27-Sep-15 7:58am    
5'ed
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)[^]
 
Share this answer
 
v2
Comments
Mehdi Gholam 27-Sep-15 7:58am    
5'ed
Wendelius 27-Sep-15 10:48am    
Thanks Mehdi!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900