What
are the differences between Temporary Table and Temporary Variable
[Temporary
Table vs Temporary Variable]?
Temporary Table
|
Temporary Variable
|
|
Use
|
Temporary Tables are tables that are temporarily created for a particular session. Once
the session is terminated, the Temporary
Tables are automatically deleted.
NOTE:
The million of records to be stored to temporary table for further operations, this case you to go with Temporary Table instead of Table Variable.
There are two types of Temporary Table in SQL Server.
1.
Local Temporary Table
Local Temporary Table’s name started with single hash ("#") sign.
CREATE TABLE #Customer
2.
Global Temporary Table
Global Temporary Table’s name started with double hash ("##") sign.
CREATE TABLE ##Customer
|
Table variables are laid out like tables and act like variables. It exists for a
particular batch of query execution. Table
variable gets dropped once it comes out of batch.
NOTE:
Table Variable name started with single at ("@") sign.
-- Create Table Variable
DECLARE @Customer
TABLE
|
Storage Location
|
Temporary Tables are the physical
tables, which are created suffixed with dynamic number under Temporary Tables
section in tempdb database in SQL Server.
|
Temporary Variables are partially stored both in the
memory and in the disk in the tempdb database.
|
Syntax
|
EXAMPLE:
Local Temporary Table:
-- Create Local Temporary Table
CREATE TABLE #Customer (
CustomerID INT,
CustomerName VARCHAR(50),
[IsActive] [bit] NOT
NULL
CONSTRAINT DF_Customer_IsActive DEFAULT (1)
CONSTRAINT
[PK_Customer_CustomerID]
PRIMARY KEY CLUSTERED
( CustomerID ASC) ON [PRIMARY]
) ON [PRIMARY]
--Insert records
INSERT INTO #Customer
(CustomerID, CustomerName)
VALUES(1,'Govindaraj')
INSERT INTO #Customer
(CustomerID, CustomerName)
VALUES(2,'Krishnaraj')
--Reterive the records
SELECT * FROM
#Customer WITH (NOLOCK)
--Drop Temporary Table
DROP TABLE #Customer
GO
Global Temporary
Table:
-- Create Global Temporary Table
CREATE TABLE ##Customer (
CustomerID INT,
CustomerName VARCHAR(50),
[IsActive] [bit] NOT
NULL CONSTRAINT
DF_Customer_IsActive DEFAULT (1)
CONSTRAINT
[PK_Customer_CustomerID]
PRIMARY
KEY CLUSTERED
( CustomerID ASC) ON [PRIMARY]
) ON [PRIMARY]
--Insert records
INSERT INTO ##Customer
(CustomerID, CustomerName)
VALUES(1,'Govindaraj')
INSERT INTO ##Customer
(CustomerID, CustomerName)
VALUES(2,'Krishnaraj')
--Reterive the records
SELECT * FROM
##Customer WITH
(NOLOCK)
--DROP Constraints
ALTER TABLE ##Customer
DROP CONSTRAINT
PK_Customer_CustomerID
--DROP Constraints
ALTER TABLE ##Customer
DROP
CONSTRAINT DF_Customer_IsActive
--Drop Temporary Table
DROP TABLE ##Customer
GO
|
EXAMPLE:
-- Create Table Variable
DECLARE @Customer TABLE
(
CustomerID INT
PRIMARY KEY,
CustomerName VARCHAR(50),
[IsActive] [bit] NOT
NULL DEFAULT(1)
)
--Insert records
INSERT INTO @Customer
(CustomerID, CustomerName)
VALUES(1,'Govindaraj')
INSERT INTO @Customer
(CustomerID, CustomerName)
VALUES(2,'Krishnaraj')
--Reterive the records
SELECT * FROM
@Customer
GO
Add a Composite Primary to Temporary Table:
DECLARE @CustomerProduct TABLE(
CustomerID INT,
ProductID INT,
IsActive bit NOT NULL DEFAULT(1),
PRIMARY KEY (CustomerID,ProductID ASC)
)
|
Support
DDL?
|
After creating the Temporary
Table it implies OR we can use DDL statements ALTER, CREATE and DROP commands
for modifying/dropping the structure/constraints explicitly.
EXAMPLE:
--Create Temporary Table
CREATE TABLE #Customer (
CustomerID INT,
CustomerName VARCHAR(50)
CONSTRAINT
[PK_Customer_CustomerID]
PRIMARY KEY CLUSTERED
( CustomerID ASC) ON [PRIMARY]
) ON [PRIMARY]
--Add Address Column
ALTER TABLE #Customer
ADD CustomerAddress VARCHAR(400)
--DROP Constraints
ALTER TABLE #Customer DROP CONSTRAINT
PK_Customer_CustomerID
--DROP Constraints
ALTER TABLE #Customer
DROP
CONSTRAINT DF_Customer_IsActive
--DROP Temporary Table
DROP TABLE #Customer
GO
NOTE:
The Temporary Table session
is still alive then other user can’t create the Temporary Table’s constraints
(Primary Key and other constraints) with the existing constraint name, this case
it will through the error ‘There is already an object named
' XXXXXXXXX ' in the database’ while creating the table with constraints.
|
Table Variables doesn’t support DDL statements like ALTER,
CREATE, DROP etc, implies we can’t modify the structure of Table Variable nor we can drop it
explicitly.
|
Support
Transaction?
|
Temporary Tables support the explicit transactions [Begin Trans, Commit Trans and Rollback] defined by the
user.
|
Temporary Variables do not participate in the transactions that have
been explicitly defined by the user.
|
Support
Index?
|
We can create the Clustered Indexes and Non- Clustered Indexes
on Temporary Table explicitly.
|
We can’t create on Indexes on Temporary Variable explicitly but we can add PRIMARY KEY and Other
constraints in column level while declaring the table variables.
|
Locking?
WITH NOLOCK
|
Since the Temporary
Tables are physical tables, while reading from the table, SQL Optimizer
puts a read lock on the Temporary Table.
EXAMPLE:
--Reterive the records
SELECT * FROM
#Customer WITH (NOLOCK)
|
Since the Table
Variables are partially stored in the memory, they cannot be accessed by
any other user or process that the current user. Therefore, no read lock is
put on the Table Variable.
EXAMPLE:
--Reterive the records
SELECT * FROM
@Customer
|
SCOPE
|
Local Temporary Table:
These tables are only
available for the session that has created them. Once the session is
terminated, these tables are automatically deleted. They can be also be
deleted explicitly.
Global Temporary Table:
These tables are
available for all the sessions and users. They are not deleted until the last
session using them is terminated. Similar to local Temporary Table, a user
can delete them explicitly.
|
Temporary Variable:
They can be declared in batch or stored procedure. Unlike
Temporary Tables, they cannot be dropped explicitly. Once the batch execution
is finished, the Table Variables are dropped automatically.
|
