This article lists out
- Difference between Temporary Table and Table Variable.
- Global Temporary Table Declaration
- Indexing Temporary Table and Table Variable.
| Temp Table | Table Variable |
| Below is the sample example of Creating a Temporary Table,
Inserting data, Selecting Data, Droping table.
--
Create Temporary Table
CREATE
TABLE
#Sample
(
Id
INT,
StdName
VARCHAR(50)
)
GO
--Inserting
Records
INSERT
INTO
#Sample
SELECT
1,'Sanoop'
UNION
ALL
SELECT
2,'Vasanth'
GO
--Retrieve
the records
SELECT
*
FROM
#Sample
GO
--DROP
Temporary Table
DROP
TABLE
#Sample |
Below is the sample example of Declaring a Table Variable,
Inserting records into it and selecting the rows from it.
--
Create Table Variable
DECLARE
@Sample TABLE
(
Id
INT,
StdName
VARCHAR(50)
)
--Inserting
Records
INSERT
INTO
@Sample
SELECT
1,'Sanoop'
UNION
ALL
SELECT
2,'Vasanth'
--Retrieve
the records
SELECT
*
FROM
@Sample |
| Possible to applay DDL statements ALTER, CREATE, DROP, After
table declaration
--
Create Temporary Table
CREATE
TABLE
#Sample
(
Id
INT,
StdName
VARCHAR(50)
)
GO
--Inserting
Records
INSERT
INTO
#Sample
SELECT
1,'Sanoop'
UNION
ALL
SELECT
2,'Vasanth'
GO
--Add
New Column
ALTER
TABLE
#Sample
ADD
Age INT
GO
--Retrieve
the records
SELECT
*
FROM
#Sample
GO
--DROP
Temporary Table
DROP
TABLE
#Sample |
Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP after its declaration. |
| Possible to create index explicitly,after table declaration
--
Create Temporary Table
CREATE
TABLE
#Sample
(
Id
INT,
StdName
VARCHAR(50)
)
GO
--Inserting
Records
INSERT
INTO
#Sample
SELECT
1,'Sanoop'
UNION
ALL
SELECT
2,'Vasanth'
GO
--Creating
NONCLUSTERED index
CREATE
NONCLUSTERED
INDEX
IndX_Name ON
#Sample([StdName]);
GO
--Retrieve
the records
SELECT
*
FROM
#Sample
GO
--DROP
Temporary Table
DROP
TABLE
#Sample |
Not possible to create Index explicitly after table
declaration. only possible indexing during declaration
--Create
Table Variable with primary key
DECLARE
@Sample TABLE
(
Id
INT
PRIMARY
KEY,
StdName
VARCHAR(50),
INDEX
IndX_Name NONCLUSTERED
(StdName)
)
--Inserting
Records
INSERT
INTO
@Sample
SELECT
1,'Sanoop'
UNION
ALL
SELECT
2,'Vasanth'
--Retrieve
the records
SELECT
*
FROM
@Sample
|
| Temporary Tables are not allowed in User Defined Functions | Table Variables can be used in User Defined Functions. |
| 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. Scope of Global Temporary Table is
not only to the session which created, but they will visible to
all other sessions. Or you can access Global Temporary Table out
side the procedure
/*Create
and dropping Global temporary table*/
CREATE
TABLE
##Sample
(
Id
INT,
StdName
VARCHAR(50)
)
DROP
TABLE
##Sample
|
Scope of the Table variable is the Batch or Stored Procedure in which it is declared |
No comments:
Post a Comment