Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wbb1975
Starting Member
23 Posts |
 Posted - 2004-09-13 : 08:23:27
|
| Recently I came accross the problem that I needed to use a temporary table within a user defined function, which leads to the error:"Cannot access temporary tables from within a function."So I was looking for a workaround which was quite simple.I am not using a local variable DECLARE @myTable TABLEin the user defined function instead of the temporary table and everything works fine.So my questions are:1.) What are the perfomance impacts of using a variable with the type "table" instead of the temporary table.2.) Why not to use table-variables generally instead of temporary tables (e.g. in complex-stored-procedures)??GreetingsStefan |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-13 : 08:43:09
|
| table variables are stored in memory, so at a certain point it becomes counter productive (i think I've heard 10,000 as a number to stay under). You can't index or create constraints of a table variable while you can on a temp table.However in a function, you only have the option of using a table variable.Corey |
 |
|
|
|
|
|
User defined functions and temporary tables