SQL Server Temp table vs Table Variable

Question

Our client's database admins have requested that we don't use temp tables within our reporting stored procedures (#Table), but instead, make use of table variables.

Are table variables less efficient than temp tables?

Also, if I create a table as #table, as opposed to ##table, the one with one # is a session table, as opposed to the ## which is global, right? When the stored procedure is completed, and you don't do a DROP TABLE #table ... does #table still exist? If it's session based, then will I ever have access to it again?

NOTE: table variables do not have statistics, and do not take part in transactions. Things to bear in mind. — Mitch Wheat, Aug 09 '11 at 05:42
There's a mistaken belief among a lot of people that table variables are always in memory, whereas temp tables go in tempdb and hit the disk. Neither of these are strictly true (they actually both go in tempdb, both will stay in memory if possible, both will spill to disk if required) — Damien_The_Unbeliever, Aug 09 '11 at 07:00
possible duplicate of [What's the difference between a temp table and table variable in SQL Server?](http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server) — Makoto, Apr 19 '13 at 00:42

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

Table variables can lead to fewer stored procedure recompilations than temporary tables (see KB #243586 and KB #305977), and — since they cannot be rolled back — do not bother with the transaction log.

##table is belogs to global temporary table. yes #table not exist because its in given scope only and you never access it out the given scope.

Edit

I also like to point make use of CTE(Common Table Expressions) because it also somehow work as temporary table. Check this answer for detail : Which are more performant, CTE or temporary tables?

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 09 '11 at 04:58

Pranay Rana

175,020
35
237
263

Given the question "yes #table not exist because its in given scope only and you never access it out the given scope" is misleading. #table exists at session scope. As such you can access it again from another stored procedure called in the same session. This means it has to hang around longer than a table variable before it gets cleaned up. – Martin Brown Jun 29 '17 at 15:58

score 0 · Answer 2 · answered Aug 09 '11 at 05:00

I'm not 100% sure what you're asking, since your title mentions Table Variables, you're asked to use Table Variables, but your question asks nothing about Table Variables... But table variables are declared like:

DECLARE @Banana TABLE 
(
  Id INT,
  Name VARCHAR(20)
)

score -1 · Answer 3 · edited Aug 09 '11 at 09:25

If local temporary table (#table) was created in SP, it is dropped after SP is finished. BOL says:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

SQL Server Temp table vs Table Variable

3 Answers3

Edit

Linked