SQL Server has 'table variables' that doesn't exist in Oracle. In Oracle, we may use a cursor to get the same task done. In addition, it seems worth taking a note on the difference between Table Variables and Temp Tables in SQL Server.
(1) How to create and use a Table variable : Primary Key, Unique Key and Not Null allowed.
DECALRE @tableVariableName TABLE
(
Column1 INT IDENTITY(1,1),
Column2 VARCHAR(10) NOT NULL,
Column3 MONEY,
...
)
INSERT INTO @tableVariableName
SELECT customerID, name, salary
FROM customers
SELECT * FROM @tableVariableName
(2) Differences between Temp tables and Table Variables
- DDL operation - Temp tables can be altered with a DDL operation while Table variables cannot be.
- Statistics - SQL Server collects statistices for temp tables but not for table variables.
- Data access - SQL Server uses various strategies to access temp tables with the table statistices collected (index density, distribution, selectivity, etc). But it accesses table variables through a table scan only.
- Performance - many people say that we get slightly better performance benefit with a table variable when working on a small data set (< 100,000 rows). But for a bigger data sets, a temp table seems to be a better choice.
