|
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.
|
Monday, 13 July 2015
Difference Between Temporary Tables and Table Variables
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment