Table variables are new to SQL Server 2000. We will first do a
comparison with cursors and then look at temporary tables. I have tested this with Windows XP Professional and SQL Server 2000.
Cursors are for iterative (3 GL) processing while table variables can be used for all set based operations. Even though there are situations where cursor based solutions are appropriate, in recent times I have seen junior developers avoiding cursors completely, as if there was some hidden rule. Somewhere, the senior community seems to have driven home the message that cursors are always bad. Trying to write set based solutions for non-set based applications only leads to unmaintainable complex SQL.
Anyway, I am not here to suggest the usage of cursors. My objective is just to make it clear that cursors and table variables have their own suitable applications and these two features are not comparable to each other. Now let us compare table variables with temporary tables.
Use Query Analyzer to run the following commands. Run them one by one and observe the messages, along with my explanations.
declare @tbl_Var1 table(f1 int, f2 varchar(10))
This one works fine with a message: Command(s) completed successfully.use [your-own-db] create table #tbl_Var1(f1 int, f2 varchar(10))
same with that one, no problem.use [tempdb] select * from sysobjects where name like '%tbl%'
This will show you one record from the [tempdb] database (assuming there is no other table with the letters 'tbl' in them). This indicates while the table variable has no internal representation in sysobjects, a temporary table does.use [your-own-db] declare @tbl_Var1 table(f1 int, f2 varchar(10))
The successful execution of the previous statement proves that the table variable you created just a few minutes back has gone out of scope and is no longer available. Table variables follow all scope rules, applicable to variables.create table #tbl_Var1(f1 int, f2 varchar(10))
While you could create a table variable with the same name, in the same session, because the earlier variable was no longer available. You cannot create a temporary table, without dropping the old one. The results should be something like this:Server: Msg 2714, Level 16, State 1, Line 11
There is already an object named '#tbl_Var1' in the database.
Server: Msg 134, Level 15, State 1, Line 9
The variable name '@tbl_Var1' has already been declared. Variable names must be unique within a query batch or stored procedure.
The following list should give you an idea of the main differences between table variables and temporary tables:
- Temporary tables have a physical representation in the database, and so carry with them all the baggage related to transaction isolation, locking and logging.
- Use temporary tables in a stored procedure and you will end up having as many copies of the table as the number of users in the system. So scaleability becomes an issue.
- To identify copies of the table used by different users, SQL Server suffixes the name of the table with a numeric value. Somewhere the Microsoft documentation says, if there are two tables with the same name, it is not
guaranteed against which table the query is resolved against. ha!
- table variables could result in fewer recompilations than when temporary tables are used.
Here is at least one instance to prove the last point. create the following stored procedures, as is.
alter procedure jk_Proc_tbl(@p_intVal int)
--declare @var2 int
declare @tbl_Var1 table(f1 int unique, f2 varchar(10))
insert into @tbl_Var1 values(@p_intVal,'aaa')
insert into @tbl_Var1 values(@p_intVal + 1,'bbb')
insert into @tbl_Var1 values(@p_intVal + 2,'ccc')
alter procedure jk_Proc_temp(@p_intVal int)
--declare @var2 int
create table #tbl_Var1(f1 int, f2 varchar(10))
insert into #tbl_Var1 values(@p_intVal,'aaa')
insert into #tbl_Var1 values(@p_intVal + 1,'bbb')
insert into #tbl_Var1 values(@p_intVal + 2,'ccc')
Now start a trace to track SP:Recompilation and execute the following statements, one at a time.
You may see SP Recompilation traces because both the SPs are being executed for the first time. Now uncomment the commented statements inside the stored procedures and create them again. And then execute the following
You will observe that the Table Variable version will not undergo a recompile, but the temporary variable
version will. This is only one instance where I have observed a recompile for sure happens. But there are various
other situations which will force a recompile.
Here is yet another advantage of using Table Variables over temporary Variables.
- If temporary tables are used within a function, there is no way to access it outside of the function, once the function terminates. With table variables, you could return the table variable to the caller and the caller could use the returned table in any context, he/she could use a normal table, as in
alter FUNCTION jk_f1_tbl(@param1 int)
RETURNS @t1 TABLE (f1 int, f2 varchar(10))
INSERT into @t1 values(@param1,'aaa')
INSERT into @t1 values(111,'aaa')
select * from jk_f1_tbl(222)
Need an order by?
select * from jk_f1_tbl(222)
order by 1
Aah, now you have beaten the views too! Please note that you cannot use an ORDER BY clause in Views (at least until SQL 2000)
Need any more reasons to use table variables?
Nothing comes without caveats! You cannot use Table Variables in the following situations:
select xxx into @table_variable or insert into @table_variable select * from myTable
For example if you want to get the output of a Stored Procedure into a table variable, you cannot do so without circumvention. Here is a sample piece of code which gets the output of a Stored Procedure in to a temporary variable. The only way to get the output of a stored procedure into a table variable is by using an intermediary table, for example using a temporary table.
set nocount on
create table #sp_depends (
insert into #sp_depends execute sp_depends myTable
declare @t1 table (ta varchar(200), tb varchar(200))
insert into @t1 select * from #sp_depends
drop table #sp_depends
select * from @t1
As a round up my recommendation would be to use Temporary tables only when table variables cannot accomplish the task at hand, and cursors of course, you should use only when set based logic is completely inapplicable or inappropriate, which we very well know is very very rare. And use Table variables whenever possible and as much as possible. Table Variables are a big boon to T-SQL. Do multidimensional arrays ring a bell?
Thankz for reading!