#table vs. @Table

SQL Server tables: what is the difference between @, # and ##?

Ø  #table refers to a local (visible to only the user who created it) temporary table.

Ø  ##table refers to a global (visible to all users) temporary table.

Ø  @variableName refers to a variable which can hold values depending on its type.

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

Ø  The first difference is that transaction logs are not recorded for the table variables.

After declaring our temporary table #T and our table-variable @T, we assign each one with the same “old value” string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same “new value” string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the “old value” string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

Example:

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select ‘old value #’ 
insert into @T select ‘old value @’ 
begin transaction 
     update #T set s=’new value #’ 
     update @T set s=’new value @’ 
rollback transaction 
select * from #T 
select * from @T 

s 
————— 
old value # 

s 
————— 
new value @

Ø  The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

But let’s compare both in terms of performance.

At first, we prepare a test table with 1 million records:

create table NUM (n int primary key, s varchar(128)) 
GO 
set nocount on 
declare @n int 
set @n=1000000 
while @n>0 begin 
     insert into NUM 
          select @n,’Value: ‘+convert(varchar,@n) 
     set @n=@n-1 
     end 
GO

Conclusion

1.    1. There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment.

2.    2. In your tests, verify both sides of the spectrum—small numbers of records and huge data sets.

3.    3. Be careful with migrating to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL server 2005!