asked on

What is the difference between a #Temp table and an @Table variable in MS SQL?

What is the difference between a #Temp table and an @Table variable in MS SQL?

I understand that both are crated in the TempDB. I also understand the creation and scope difference between @Table, #Table and ##Table.

But in simple/straightforward terms, what is the real difference?

When would I use one over the other?

Thanks,
JohnB

ASKER CERTIFIED SOLUTION

p_nuts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vijaya Reddy Pinnapa Reddy

Check this articles,

http://www.venkateswarlu.co.in/articles/SQL/Differences_between_SQL_Server_temporary_tables_and_table_variables.aspx
http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
http://weblogs.sqlteam.com/mladenp/archive/2007/05/13/60204.aspx

Hamed Nasr

Simply said: If you don't have to worry about maintaing tables in database use #temp table.
It is created as a normal table but is managed by the database, will be cleared when you exit SQL.

@t is a temporary table variable returned from a table valued function. @t =@retuen_table in the following example

if object_id(N'ufn_multistatement_table_valued',N'TF') is not null
	drop function dbo.ufn_multistatement_table_valued  ;
go
create function dbo.ufn_multistatement_table_valued 
(@parameter_name  nvarchar(25))
RETURNS @retuen_table TABLE (a nvarchar(25) Primary Key,r nvarchar(25))
WITH EXECUTE AS CALLER
AS
BEGIN
	declare @a nvarchar(25)
	set @a='3'
	INSERT @retuen_table SELECT a, r FROM A WHERE a>@parameter_name
	RETURN
END;
GO
SELECT * from dbo.ufn_multistatement_table_valued('1');

Open in new window

@v is a temporary variable, you can use to manage your table records.

declare @v Int;
select @v=a from A where a=1;
select @v; -- you can manipulate @v and modify the records.

Open in new window