views:

37

answers:

4

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.

+1  A: 

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.

Noel Abrahams
**Define = create and possibly populate.** Make sure you use `##tableName` to ensure the table is accessible globally (outside of the current SPID.
Brad
is Table type in sqlserver 2008 faster ???
Anish
+1  A: 

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.

marc_s
is Table type in sqlserver 2008 faster ???
Anish
@Anish: there's no generally clear answer; you need to try it and measure for yourself - it depends on your structure, your data, the amount of data, your hardware.... just too many factors - cannot say for sure - test and measure!
marc_s
A: 

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.

Learn more about it from here:

http://msdn.microsoft.com/en-us/library/ms131103.aspx

franklins
A: 

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

How to Share Data Between Stored Procedures by 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
KM