Thursday, 25 October 2018

Difference Between Temporary Table and Table Variable in Sql Server

Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in SQL Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.
1. SYNTAX
Below is the sample example of Creating a Temporary Table, inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table.
-- Create Temporary Table
CREATE TABLE #Customer
(Id INT, Name VARCHAR(50))

--Insert Two records
INSERT INTO #Customer
VALUES(1,'Basavaraj')
INSERT INTO #Customer
VALUES(2,'Kalpana')

--Retrieve the records
SELECT * FROM #Customer

--DROP Temporary Table
DROP TABLE #Customer
Below is the sample example of Declaring a Table Variable, inserting records into it and retrieving the rows from it.
-- Create Table Variable
DECLARE @Customer TABLE
(
 Id INT,
 Name VARCHAR(50)  
)

--Insert Two records
INSERT INTO @Customer
VALUES(1,'Basavaraj')
INSERT INTO @Customer
VALUES(2,'Kalpana')

--Retrieve the records
SELECT * FROM @Customer
2. MODIFYING STRUCTURE
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP.
Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped.

--Create Temporary Table
CREATE TABLE #Customer
(Id INT, Name VARCHAR(50))
GO
--Add Address Column
ALTER TABLE #Customer
ADD Address VARCHAR(400)
GO
--DROP Temporary Table
DROP TABLE #Customer
GO
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.
3. STORAGE LOCATION
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
4. TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.
Table variables doesn’t participate in the explicit transactions defined by the user.
5. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.
Table Variables can be used in User Defined Functions.
6. INDEXES
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
7. SCOPE
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs. Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure. Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.
Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes, or the Stored Procedure execution completes.



5 comments:

  1. Amazing Article ! I have bookmarked this article page as i received good information from this. All the best for the upcoming articles. I will be waiting for your new articles. Thank You ! Kindly Visit Us @ Coimbatore Travels | Ooty Travels | Coimbatore Airport Taxi | Coimbatore taxi

    ReplyDelete
  2. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Advanced AWS Training in Bangalore | Best Amazon Web Services Training Institute in Bangalore
    Advanced AWS Training Institute in Pune | Best Amazon Web Services Training Institute in Pune
    Advanced AWS Online Training Institute in india | Best Online AWS Certification Course in india
    AWS training in bangalore | Best aws training in bangalore

    ReplyDelete
  3. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's. 
    rpa training in bangalore
    rpa training in pune
    rpa online training
    best rpa training in bangalore

    ReplyDelete
  4. I read this post two times, I like it so much, please try to keep posting & Let me introduce other material that may be good for our community.
    Best Devops online Training
    Online DevOps Certification Course - Gangboard
    Best Devops Training institute in Chennai

    ReplyDelete
  5. Does your blog have a contact page? I’m having problems locating it but, I’d like to shoot you an email. I’ve got some recommendations for your blog you might be interested in hearing.
    python course institute in bangalore
    python Course in bangalore
    python training institute in bangalore

    ReplyDelete