Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Table Variables

By Jambu Krishnamurthy,

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.

use [your-own-db]
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:
  1. Temporary tables have a physical representation in the database, and so carry with them all the baggage related to transaction isolation, locking and logging.
  2. 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.
  3. 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!
  4. 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)
as
begin
	--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')
end

alter procedure jk_Proc_temp(@p_intVal int)
as
begin
	--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')
end
Now start a trace to track SP:Recompilation and execute the following statements, one at a time.
jk_Proc_tbl 111

jk_Proc_temp 111
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
jk_Proc_tbl 111

jk_Proc_temp 111
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))
    AS
    BEGIN
    	INSERT into @t1 values(@param1,'aaa')
    	INSERT into @t1 values(111,'aaa')
    
    	RETURN
    END
    
    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 (
      t1		nchar(128),
      t2		nchar(128)
      )
    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
    

    Conclusion

    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!

Total article views: 16342 | Views in the last 30 days: 17
 
Related Articles
FORUM

Temporary variables in SQL

Improve the SQL query performance with temporary variables

ARTICLE

Comparing Table Variables with Temporary Tables

This articles brings a comparison of temporary tables with table variables from SQL Server author, W...

ARTICLE

Local Temporary Tables and Table Variables

Peter He examines the differences between temporary tables and table variables in SQL Server, showin...

BLOG

SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for differen...

FORUM

Create Temporary Tables Dynamically in cursor loop

How can I create n temporary tables dynamically?

Tags
miscellaneous    
performance tuning    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones