Some days back i was asked about Temp Tables and Table Variables the basic difference why one is better than another one and all, and i am not able to provide a satisfactory reason\answer at that time so this is the time when i studied about a lots about it and i go through several topics, it takes one whole day and night to me to understand some aspects of this and here i am writing a detailed post about Temp Tables and Table Variables which may help some of peoples like me to understand in a better way.
So going through very basic details let talk about Temporary Tables first. So there are two types of temporary tables
A.) Local declared with one # Sign (#)
B.) Global declared with two # Sign (##)
Both of these tables created by simple CREATE TABLE (BOL) syntax except that # is used for Local Temporary Tables declaration and ## is used for Global Temporary Tables.
The Major difference between these two are Local Temporary tables (#) are visible in current connection and Global Temporary Table (##) are visible to all sessions or to the entire instance. You should always check for existence of global temporary table before creating it.
Let see with an example..
Open SSMS connect it and now create a Local Temporary table like this.
-- Creating Local Temporary Table
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
This will create a temporary table named LOCALTEST now open other SSMS connect it and again retry to create another table with same name but using the new connection and check if it created or not so here it is ….
-- Create Temporary Table with same name
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
-- Try to find out table with 'LOCALTEST' name in TempDb
USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like '%LOCALTEST%'
GO
So here is the result set for the above query
So its very clear from the result set that there are two Local Temporary Tables with the same name.
In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number, you can see that in this image.
But when you try the same thing with Global Temporary Tables then it will result into an error.
So here is some points about Temporary tables..
- Can be create by simple Create Table Syntax use # for Local Temporary Table and ## for Global Temporary table.
- Local temporary tables will be dropped at the end of current session but if it is created inside a store procedure, it will be dropped when store procedure is finished.
- Its name is limited to 116 characters.
- Foreign Key constraints can’t be applied to primary tables.
Now we know a little about Table Variables :
Table Variables (@) introduced first in SQL Server 2000 and onwards as name suggest its a variable of type table. Its definition’s include column definition, names, data type and constraint(Foreign Key Constraint are not allowed). They are more flexible and and always stay in memory well this is not true that table Variables stay always in memory this is a MYTH as table variables may be stored as in same way in TempDb as temporary tables we will look at some example for this. They are created with the Declare @variable syntax. There are some properties for Table Variables
- They have well defined limited scope and are not part of persistent database.
- Transactional rollbacks doesn’t affect on them.
- They have very limited scope like current batch of statements so they can be deleted automatically after the completion of statements.
- You don’t use them in nested procedures.
- You not able to truncate a table variable.
So these are the some of basics for Table Variables and Temporary Tables but our Aim is to be find out which one is best in terms of performance, Advantages & Disadvantages for each and some considerations so we try to understand things why one is better then another and why not so
A.) Table Variables have a limited scope and are not part of persistent database, transaction rollback do not affect them.
Let’s understand what does it mean by transaction rollback do not affect them just take a simple example for this..
-- Create Tempoarary Table
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(50))
-- Declare Table Variable
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(50))
-- Insert Values into both
Insert into #LOCALTEST SELECT 1 ,'Ashish'
Insert into @LOCALTEST SELECT 1 ,'Ashish'
-- Begin Transaction (Update both Temp table & Table Var)
BEGIN TRAN
UPDATE #LOCALTEST SET C2='ASHISH GILHOTRA'
UPDATE @LOCALTEST SET C2='ASHISH GILHOTRA'
-- Rollback Tran
ROLLBACK TRAN
-- Check data
SELECT * FROM #LOCALTEST
SELECT * FROM @LOCALTEST
and here is the result set
From this it’s very clear that Table Variables doesn’t take part in rollback transactions.So it may be or may not be useful for developers depends on the developers.
B.) There is less locking and logging on table variables then temporary tables so what does it means so we take an another example to understand this ..
USE tempdb
GO
-- Drop table #LOCALTEST
-- Drop table DUMMYDATA
-- Create a dummy table
CREATE TABLE DUMMYDATA (A int, B varchar(100))
-- Populate Data in it
Declare @i int
set @i=1
while @i<1001
BEGIN
INSERT into DUMMYDATA SELECT @i,REPLICATE('A',100)
SET @i=@i+1
END
--select * from DUMMYDATA
--Using #LOCALTEST
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))
GO
BEGIN TRAN
INSERT INTO #LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- Using @LOCALTEST
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))
BEGIN TRAN
INSERT INTO @LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- Now check for locks
select request_session_id, resource_type, db_name(resource_database_id),(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description,
request_mode,
request_status
from sys.dm_tran_locks
--Rollback tran
So if you going to run this query batch you will see lock acquire by “#LOCALTEST” not by @LOCALTEST so yes that true that Table Variables takes less locking then temporary tables.
for logging purpose just populate temp Table and Table Variable with same data as shown above and now use fn_dblog(Un Documented) to see what happen like this..
So here is a way to find logging
First of all see logging for Table Variables
-- Table Variable
DECLARE @LOCALTEST TABLE(C1 INT, C2 VARCHAR(100))
INSERT INTO @LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- update all the rows
update @LOCALTEST set C2 = replicate ('AB', 50)
-- Look at the top 10 log records.
select top 10 operation, AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%LOCALTEST%'
order by [Log Record Length] Desc
For this i don’t get any records of update log in this and try same for Temporary Tables you will get update logs in this..
-- Temp Table
--drop table #localtest
CREATE TABLE #LOCALTEST(C1 INT, C2 VARCHAR(100))
GO
INSERT INTO #LOCALTEST(C1,C2)
SELECT A,B FROM DUMMYDATA
-- update all the rows
update #LOCALTEST set C2 = replicate ('ab', 50)
-- Look at the log records. Here you get log records for update
select operation,AllocUnitName
from fn_dblog(null, null)
where AllocUnitName like '%LOCALTEST%'
order by [Log Record Length] Desc
And at last about the performance and IO usage for temporary table and table variables i use that example so the values vary for user to user so you can try the same on your systems and see what results you have with this…i am still working on this and as soon as possible i will be back with some results valid and good results to this till then you can try this at your end…
USE AdventureWorks
-----------------------------------------------------------------------
DECLARE @TEMP TABLE
(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO @TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM @TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------
CREATE TABLE #TEMP(ADDRESSID INT ,ADDRESSLINE1 VARCHAR(100)
,ADDRESSLINE2 VARCHAR(100),CITY VARCHAR(30),StateProvinceID INT)
INSERT INTO #TEMP (ADDRESSID,ADDRESSLINE1,ADDRESSLINE2,CITY,StateProvinceID)
SELECT TOP 100 ADDRESSID,ADDRESSLINE1
,ADDRESSLINE2,CITY,StateProvinceID FROM PERSON.ADDRESS
--SELECT * FROM @TEMP
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT a.*
FROM #TEMP A INNER JOIN PERSON.STATEPROVINCE B
ON A.StateProvinceID=B.StateProvinceID
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DROP TABLE #TEMP
----------------------------------------------------------------
There are some more things like You can’t Alter Table Variable definition no DDL is applied on Table Variables and no statistics is maintained on table variable and statistics are used by query optimizer so there is a performance issue with this.
So after that experiments i think that this is totally depends on the developer that what he wants to use and what are the requirements. i will made a follow up post on this topic again as i may miss many of things on this vast topic so i really appreciate your suggestions and reviews on this if you found anything which i miss or wrong in this then drop a comment here we will try to make it clear as soon as possible and soon as i got more information about this i will write a follow up post soon.
Thanks