# temp table and @ temp table access speed

Question

Hi,

Some people suggest that # temp table is good for huge dataset while the @ temp variable is fast for small dataset.
My question is how do we define the huge and small dataset?
Thanks for help.

Jason

Answer 1

So you question is how big is big?  Well, it depends :-)

The main advantages of table variables are limited scope and reduced logging.  The advantages of temp tables include statistics and allowing non-unique indexes.  Indexes and statistics are major contributors to performance but much depends on how you use the temporary data in your queries before you can decide the best tool for the job at hand.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Small - less than 1K rows. Everything which is bigger I will consider using temp tables instead of table variable.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Hi,

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).

CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

Assignment operation between table variables is not supported.

Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.

For more information use below URL

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

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

i also prefer temp table over temp variable, although you will use a very small dataset you can yourself check that there are very nominal affect and improvement. so temp table ranks higher to me.

Answer 5

Difference TempTable and Table Variable – TempTable in Memory a Myth

December 15, 2009 by pinaldave

Recently, I have been conducting many training sessions at a leading technology company in India. During the discussion of temp table and table variable, I quite commonly hear that Table Variables are stored in memory and Temp Tables are stored in TempDB. I would like to bust this misconception by suggesting following:

Temp Table and Table Variable — both are created in TempDB and not in memory.

Let us prove this concept by running the following T-SQL script.

/* Check the difference between Temp Table and Memory Tables */
-- Get Current Session ID
SELECT @@SPID AS Current_SessionID
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Create Temp Table and insert three thousand rows
CREATE TABLE #TempTable (Col1 INT)
INSERT INTO #TempTable (Col1)
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Create Table Variable and insert three thousand rows
DECLARE @temp TABLE(Col1 INT)
INSERT INTO @temp (Col1)
SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the space usage in page files
SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = (SELECT @@SPID )
GO
-- Clean up
DROP TABLE #TempTable
GO

Let us see the resultset. It is very clear that the size of the table variable and temp table is the same and created in TempDb.

Have you ever heard of this misconception? Do you know any other method to prove that both Temp Table and TableVariable are created in

TempDB.

Ref. http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 6

Hi,

I tested the #temp and @temp on my appliction, the @temp variable method is a little bit faster.  Also, the @temp method no need to clear the existed table on the Temp DB.

Jason