Monday 3 July 2017

Temporary Table vs Temporary Variable


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.