Difference Between Temp Table and Table Variable
General
Difference Between Temp Table and Table Variable
Both Temporary Tables ( # Tables) and Table Variables (@ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
1.Temp Table
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.
Syntax Of Tamp Table
|
2. Modification OF Temp Table
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.
|
3.TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.
4. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.
5.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 Variable
Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it.
|
MODIFYING STRUCTURE
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.
TRANSACTIONS
Table variables doesn’t participate in the explicit transactions defined by the user.
USER DEFINED FUNCTION
Table Variables can be used in User Defined Functions.
INDEXES
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.
Difference Between Temp Table and Table Variable
Both Temporary Tables ( # Tables) and Table Variables (@ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
1.Temp Table
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.
Syntax Of Tamp Table
|
2. Modification OF Temp Table
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.
|
3.TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.
4. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.
5.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 Variable
Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it.
|
MODIFYING STRUCTURE
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.
TRANSACTIONS
Table variables doesn’t participate in the explicit transactions defined by the user.
USER DEFINED FUNCTION
Table Variables can be used in User Defined Functions.
INDEXES
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.
Share This with your friend by choosing any social account
You may also read following recent Post
![]() |
$1,000 cpc keywords
79 By Junaid A |
![]() |
How to grow 1% every day
119 By Junaid A |
![]() |
The setup for the installation of visual studio is not complete. Please run the Visual Studio installer again to correct the issue.
99 By Junaid A |
![]() |
Getting error "Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine"
230 By Junaid A |
![]() |
CRUD in Asp.net MVC
127 By Ihsan Ullah |
![]() |
What is life?
129 By Shoaib Khan |
![]() |
paragraph
135 By Ihsan Ullah |
![]() |
life
104 By khubaib Ahmad |
![]() |
How to start PMP
675 By Junaid A |
![]() |
what is robots.txt
664 By Junaid A |
![]() |
What is CURL?
714 By Junaid A |
![]() |
615 By shaheer |
![]() |
631 By shaheer |
![]() |
MVC LEARNIG
258 By |
![]() |
The data connection could not be established: ETIMEDOUT - Connection attempt timed out Error: Connec
262 By |
![]() |
show hide kendo ComboBox
275 By |
![]() |
Active A Class In Layout
290 By |
![]() |
Issues opening Excel documents - File format and extension of 'orders.xls' don't match. The file cou
242 By |
![]() |
Multiple errors occurred during the operation, the first of which is displayed below. A full error l
242 By |
![]() |
asp.net mvc routing tricks
254 By |
![]() |
What is routes in AngularJS?
282 By |
![]() |
Attackers might be trying to steal your information from (for example, passwords, messages, or cred
252 By |
![]() |
LINQ to Entities does not recognize the method 'System.String ToShortDateString()' method, and this
289 By |
![]() |
how to call a function in c#
302 By |