Table Variables

Table Variables
===============

Table variables are new to SQL 2000.
 
We will first clear steer of its comparison with cursors and then look at temporary tables.

It cannot be compared to cursors, as that would not be a comparison of apples to apples.
Cursors are for iterative (3 GL) processing while table variables can be used for all set 

based operations. 

On the side:
Even though there are situations where cursor based solutions are apt, 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, cursors cleared off our way, let us compare it with temporary tables.


Use the Query Analyzer to run the following commands
run them one by one and observe the messages.

use [your-own-db]
declare @tbl_Var1 table(f1 int, f2 varchar(10))

create table #tbl_Var1(f1 int, f2 varchar(10))

use [tempdb]
select * from sysobjects where name like '%tbl%'

use [your-own-db]
declare @tbl_Var1 table(f1 int, f2 varchar(10))

create table #tbl_Var1(f1 int, f2 varchar(10))


1. Temporary tables have physical representation in the database, and so carries with it 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 between different users SQL Server suffixes the name of 

the table with a numeric value. Somewhere 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 atleast one instance to prove this.

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, one at a time.

jk_Proc_tbl 111

jk_Proc_temp 111


Now uncomment the commented statements inside the stored procedures and create them again.
And then execute the following again

jk_Proc_tbl 111

jk_Proc_temp 111



5. If temporary tables are used within a function, there is no way to access it out 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!


Need any more reasons to use table variables?
Thankz for reading!