Which are the best Variables in stored procedures

I'm often dealing with some interfaces between t开发者_如何学运维wo systems with data import or data export. Therefore I'm programming some T-SQL procedures. It's is often necessary to use some variables inside these procedures, to hold some values or single records.

The last time I set up some temp tables e.g. one with name #tmpGlobals and another named #tmpOutput. The names doesn't matter but I eliminated the use of declaring some @MainID int or like that.

Is this a good idea? Is it a performance issue?

As Alexander suggests, it really depends. I won't draw hard lines in the sand about number of rows, because it can also depend on the data types and hence the size of each row. Where one will make more sense than the other in your environment can depend on several factors aside from just the size of the data, including access patterns, sensitivity of performance to recompiles, your hardware, etc.

There is a common misconception that @table variables are only in memory, do not incur I/O, do not use tempdb, etc. While in certain isolated cases some of this is true, it is not something you can or should rely on.

Some other limitations of @table variables that may prevent your use of them, even for small data sets:

• cannot index (other than primary key / unique constraint declarations on creation)
• no statistics are maintained (unlike #temp tables)
• cannot ALTER
• cannot use as INSERT EXEC target in SQL Server 2005 (this restriction was lifted in 2008)
• cannot use as SELECT INTO target
• cannot truncate
• can't use an alias type in definition
• no parallelism
• not visible to nested procs (unlike #temp tables)

It really depends on the amount of data. If you're using under 100 records, then DECLARE @MainID or the like is probably better since it's a smaller amount of data. Anything over 100 records though, you should definitely use #tmpGlobals or similar since it's better for memory management on the SQL server.

EDIT: It's not bad to use #tmpGlobals for smaller sets, just not much of a performance loss or gain from DECLARE @MainID. You will see a performance gain when using #tmpGlobals, instead of DECLARE @MainID, on a high number of records.

In general, you should choose the reverse if possible. It depends on whether you need to store a set of items or just result values.

Scoped variables, aside from table variables, are relatively cheap. Things that fit into typed variables that aren't tables, operate faster than storing them as single rows in a table.

Table variables and temp tables tend to be quite expensive. They may require space in tempdb and also offer no optimizations by default. In addition, table variables should be avoided for large sets of data. When processing large sets, you can apply indexes and define primary keys on temp tables if you wish, but you cannot do this for table variables. Finally, temp tables need cleanup before exiting scope.

For parameters, table variables are useful for return sets from functions. Temp tables cannot be returned from functions. Depending on the task at hand, use of functions may make it easier to encapsulate specific portions of work. You may find that some stored procedures are doing work that is better suited to functions, especially if you are reusing but not modifying the results.

Finally, if you just need one-time storage of results in the middle of stored-procedure work, try CTEs. These usually beat out both table variables and temp tables, as SQL server can make better decisions on how to store this data for you. Also, as a matter of syntax, it may make your declarations more legible.

Using Common-Table Expressions @ MSDN

edit: (regarding temp tables)

Local temp tables go away when the query session ends, which can be an indeterminate amount of time away in the future. Global temp tables don't go away until the connection is closed and no other users are using the table, which can be even longer. In either case, it is best to drop temp tables (as no longer needed) on exit of a procedure to avoid tying up memory and other resources.

CTEs can be used to avert this, in many cases, because they are only local to the location where they are declared. They automatically are marked for cleanup once the stored procedure or function of their scope exits.