What is the difference between Temp Table and Table Variable?


Temp table and Table Variable both are created in TempDB and not in Memory

Syntax for creating Temp Table is exactly same as creating Physical Table

Create table  #Emp(Id int, EmpName Varchar(150),EmpAdd Varchar(150))

Insert into #Emp(Id,EmpName,EmpAdd) Values(1,'Ram','Bangalore')

Select *from #Emp

The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:

Declare @Emp Table(Id int,EmpName Varchar(150),EmpAdd Varchar(150))

Insert into @Emp (Id,EmpName,EmpAdd) Values(2,'Ram','Bangalore')

Select *from @Emp


• Table variables can not have Non-Clustered Indexes
• You can not create constraints in table variables
• You can not create default values on table variable columns
• Statistics can not be created against table variables

Similarities with temporary tables include:

• Instantiated in tempdb
• Clustered indexes can be created on table variables and temporary tables
• Both are logged in the transaction log
• Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

Table variable lifespan is only for the duration of transaction that it runs in. For executing the Table variable we have to execute the complete block code. Otherwise it will throw the exception.

When to use Table variable?

Table variable is mainly used in the function. We cannot use Temp table in function. For example

CREATE FUNCTION dbo.example1
(
)
RETURNS INT
AS
BEGIN
DECLARE @t1 TABLE (i INT)
INSERT @t1 VALUES(1)
INSERT @t1 VALUES(2)
UPDATE @t1 SET i = i + 5
DELETE @t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM @t1
RETURN @max
END
GO


--Exec example1

--Select dbo.example1()


We cannot create above example using Temp Temple like this

CREATE FUNCTION dbo.example2
(
)
RETURNS INT
AS
BEGIN
CREATE TABLE #t1 (i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
UPDATE #t1 SET i = i + 5
DELETE #t1 WHERE i < 7

DECLARE @max INT
SELECT @max = MAX(i) FROM #t1
RETURN @max
END
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: