Hi sidu,
Temp Table or CTE are commonly used for storing data temporarily in SQL Server.
CTE
CTE stands for Common Table Expressions. It is a temporary result set and typically it may be a result of complex sub-query. Unlike a temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.
A sub query without CTE is given below:
SELECT * FROM (
SELECT c.Address, e.Name, e.Age
FROM Customer c
INNER JOIN Employee e ON e.EmployeeId = c.EmployeeId) Temp
WHERE Temp.Age > 50
ORDER BY Temp.Name
By using CTE above query can be re-written as follows:
;With CTE(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT c.Address, e.Name, e.Age
FROM Customer c
INNER JOIN Employee e ON e.EmployeeId = c.EmployeeId
)
SELECT * FROM CTE --Using CTE
WHERE CTE.Age > 50
ORDER BY CTE.Name
When to use CTE: This is used to store a result of a complex sub query for further use.
Temporary Tables
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below.
Local Temp Table
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with single hash ("#") sign. The scope of Local temp table exists to the current session of the current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
CREATE TABLE #Employee
(
EmployeeId INT,
Name VARCHAR(50),
Address VARCHAR(150)
)
GO
INSERT INTO #Employee VALUES (1,'Mudassar Khan','India');
GO
SELECT * FROM #Employee
Global Temp Table
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign. Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
CREATE TABLE ##Employee
(
EmployeeId INT,
Name VARCHAR(50),
Address VARCHAR(150)
)
GO
INSERT INTO ##Employee VALUES (1,'Mudassar Khan','India');
GO
SELECT * FROM ##Employee
Note :
- Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
- CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
For more details refer below links.
https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables
https://stackoverflow.com/questions/32044832/cte-temp-table-and-table-variable