Tuesday, August 24, 2004
Difference between Table Variable and Temporary Table
Table variables were introduced in SQL Server 2000. Many times they are presented as a replacement for temporary tables. There is a lot of arguement about the performance advantages of one over the other. It is generally believed that table variables give better performance than temporary tables. But there are so many cases reported where the usage of table variable slow down the performance, especially when the number of rows involved are high. So now the general recommendation is that use a table variable only if there is a performance gain over the temp table.
Lets see what are the basic difference between them.
1. Contrary to the popular belief that table variables exists in memory, KB Article 305977 states that "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)."
The implementation of table variables is from a logging and tempdb usage very similar to temporary tables.
2. You can create indexes on a temp table. Table variables does not support explicit index creation. You can create indexes on table variables using the PRIMARY KEY and Unique constraints.
3. User defined functions are not permitted to do DDL operations. But UDFs can CREATE, INSERT, UPDATE, DELETE and RETURN table variables. (But you cannot use user-defined data types in the table variable you declare for the return value.)
4. Table variables are always local to its scope while Temporary tables can be global or local. So table variables cannot be accessed from calling procedures in case of nested sps.
5. Stored Procedure Recompilations. Using table variables can reduce the number of stored procedure recompilations. thats one place where table variables excel over temp tables.
6. Table variables do not maintain statistics like temporary tables. This might lead to bad execution plan and slow performance when dealing with higher number of rows.
7. Temp tables can be ALTERed. But you cannot change the definition of a table variable after the initial DECLARE statement.
8. Parallelism is not possible when you insert into a table variable.
9. You cannot do
INSERT INTO @tablevar
EXEC yourspname
But
INSERT INTO #temptable
EXEC yourspname
is possible.
10. You cannot do
SELECT *INTO @tableVar FROM source
But
SELECT *INTO #temptable FROM source
is possible.
11. You cannot truncate a table variable.
DECLARE @x table(a int)
INSERT INTO @x VALUES(1)
TRUNCATE TABLE @x
produces a syntax Error.
But the same is possible with temp table.
DECLARE #x table(a int)
INSERT INTO #x VALUES(1)
TRUNCATE TABLE #x
12. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources. That is table variables are automatically committed, where as temporary tables can be included in a transaction. This can make a performance difference.
13. Join Syntax. You MUST use an alias when referring a table variable in a JOIN. The following query will throw an error.
USE PUBS
GO
DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x
ON Titles.title_id = @x.someid
It should be written as
DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x a
ON Titles.title_id = a.someid
Temporary tables doesnt have such limitations.
14. Temporary tables can be explicitly destroyed using DROP statement.
15. You cannot assign a table variable to another as in the case of other variables.
16. CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
17. "Display Estimated Execution Plan" option in query analyser does'nt work with T-SQL code that has a reference to Temporary tables.
18. More, anyone...?
Lets see what are the basic difference between them.
1. Contrary to the popular belief that table variables exists in memory, KB Article 305977 states that "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)."
The implementation of table variables is from a logging and tempdb usage very similar to temporary tables.
2. You can create indexes on a temp table. Table variables does not support explicit index creation. You can create indexes on table variables using the PRIMARY KEY and Unique constraints.
3. User defined functions are not permitted to do DDL operations. But UDFs can CREATE, INSERT, UPDATE, DELETE and RETURN table variables. (But you cannot use user-defined data types in the table variable you declare for the return value.)
4. Table variables are always local to its scope while Temporary tables can be global or local. So table variables cannot be accessed from calling procedures in case of nested sps.
5. Stored Procedure Recompilations. Using table variables can reduce the number of stored procedure recompilations. thats one place where table variables excel over temp tables.
6. Table variables do not maintain statistics like temporary tables. This might lead to bad execution plan and slow performance when dealing with higher number of rows.
7. Temp tables can be ALTERed. But you cannot change the definition of a table variable after the initial DECLARE statement.
8. Parallelism is not possible when you insert into a table variable.
9. You cannot do
INSERT INTO @tablevar
EXEC yourspname
But
INSERT INTO #temptable
EXEC yourspname
is possible.
10. You cannot do
SELECT *
But
SELECT *
is possible.
11. You cannot truncate a table variable.
DECLARE @x table(a int)
INSERT INTO @x VALUES(1)
TRUNCATE TABLE @x
produces a syntax Error.
But the same is possible with temp table.
DECLARE #x table(a int)
INSERT INTO #x VALUES(1)
TRUNCATE TABLE #x
12. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources. That is table variables are automatically committed, where as temporary tables can be included in a transaction. This can make a performance difference.
13. Join Syntax. You MUST use an alias when referring a table variable in a JOIN. The following query will throw an error.
USE PUBS
GO
DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x
ON Titles.title_id = @x.someid
It should be written as
DECLARE @x table(someid varchar(15))
INSERT INTO @x
SELECT Top 10 title_id FROM Titles
SELECT * FROM Titles
INNER JOIN @x a
ON Titles.title_id = a.someid
Temporary tables doesnt have such limitations.
14. Temporary tables can be explicitly destroyed using DROP statement.
15. You cannot assign a table variable to another as in the case of other variables.
16. CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
17. "Display Estimated Execution Plan" option in query analyser does'nt work with T-SQL code that has a reference to Temporary tables.
18. More, anyone...?
Comments:
<< Home
Hi,
Your article was quite informative. but I feel you havent mentioned the good things about the table varibales. if you can write that also i feel most of your points may become null. as the table variables should be used where it is supposed to use. what do you think ?
Your article was quite informative. but I feel you havent mentioned the good things about the table varibales. if you can write that also i feel most of your points may become null. as the table variables should be used where it is supposed to use. what do you think ?
Catch22 with relational updates to temp tables; this works with #temp tables but breaks with @memory tables and theres no way to fix it because aliasing isn't allowed on the update target:
update @table3 set data = x.data from @table2 AS x where x.x = @table3.x
update @table3 set data = x.data from @table2 AS x where x.x = @table3.x
# posted by 
: 10:23 AM
: 10:23 AM
You said: "You MUST use an alias when referring a table variable in a JOIN."
That's not true: You can remove the error by putting brackets around the name of the table variable, before the period, and not use an alias.
Old article, but still relevant.
That's not true: You can remove the error by putting brackets around the name of the table variable, before the period, and not use an alias.
Old article, but still relevant.
# posted by : 11:28 PM
: 11:28 PM
Also, Lazywriter said "theres no way to fix it".
Yes, there is: [@Table3].x
David
Yes, there is: [@Table3].x
David
# posted by : 11:29 PM
: 11:29 PM
this is really a informative article. It help me lot to take decision on what to choose tempTable or Table variable
---KALLOL SAMANTA
---KALLOL SAMANTA
# posted by : 2:06 PM
: 2:06 PM
its a Nice article, but the statement INSERT @tablevar
EXEC yourspname
will work with table variables. Test this at your side
EXEC yourspname
will work with table variables. Test this at your side
# posted by : 10:16 PM
: 10:16 PM
Microsoft Office 2010
Office 2010
Microsoft Office 2007
Office 2007
Microsoft Office
Office 2007 key
Office 2007 download
Office 2007 Professional
Microsoft outlook
Microsoft outlook 2010
Office 2010
Microsoft Office 2007
Office 2007
Microsoft Office
Office 2007 key
Office 2007 download
Office 2007 Professional
Microsoft outlook
Microsoft outlook 2010
# posted by : 6:25 AM
: 6:25 AM
It is a woderful news .I have to use more of these ideas.Thank for your post, i really love it. I will come again. Here useful link:Tera items buy tera gold buy wow gold Tera Item
will work with table variables. Test this at your side cambridge satchel | cambridge satchel us | cambridge satchel | cambridge satchel company | Cheap Soccer Jersey | hermes birkin
# posted by : 9:02 AM
: 9:02 AM
I really enjoy your posts as I learn a lot from them. I also broaden my thinking as far as what I can use and do with thingsHere useful linkļ¼
diablo 3 gold
guild wars 2 gold
guild wars 2 gold
diablo 3 gold
diablo 3 gold
guild wars 2 gold
tera gold
Post a Comment
diablo 3 gold
guild wars 2 gold
guild wars 2 gold
diablo 3 gold
diablo 3 gold
guild wars 2 gold
tera gold
<< Home
