Monday, 13 July 2015

Difference Between Temporary Tables and Table Variables

TEMPORARY TABLES
TABLE VARIABLES
There are Two types Temporary tables, One is Local Temporary Tables whose name starts with Single (#) Sign and the Second one is Global Temporary Tables whose name starts with Two (##) sign.
Local Temporary Tables are visible only to the current connection for the user and they are deleted when user disconnects  or we can drop them explicitly.
Global Temporary Table are Visible to any user after they created, and they are deleted when all users referencing to the table disconnects from server or we can drop them explicitly.
These are Stored in Temp DB.
Table Variable is like a temporary tables with slight changes. Table Variables are deleted automatically after the execution. We cannot drop table variables explicitly.
These are Stored in Temp DB.
Syntax:-

CREATE TABLE #TEST (ID INT ,NAME VARCHAR(20) ,GENDER CHAR (10) ,SALARY MONEY)

Syntax:-

DECLARE @TEST TABLE (ID INT ,NAME VARCHAR(20) ,GENDER CHAR (10),SALARY MONEY )

We can Modify the structure of the table after the creation of Table.
Temporary Tables Supports DDL Statements.
We cannot Modify the structure of the table variables after the creation.
Table Variables doesn't  Support DDL Statements.
Temporary Tables Supports adding constraints after Creation of the Table. Which means Temporary Tables supports adding Indexes explicitly after Creation of Temporary Tables.
Table Variables doesn't allow the explicit addition of constraints/indexes after it's declaration. Which means we can specify indexes or constraints only while declaring the table variable itself.
Temporary Tables cannot be used in user defined Functions.
Table Variables can be used in User Defined Functions.


No comments:

Post a Comment