CTE,Temp Table And Table variable
|
indrada...
116
Points
14
Posts
|
Hello Friends, I want to know what is the key difference between CTE, Temp Table and Table variable used in SQL. thank you
Views:
8770
Total Answered: 1 Total Marked As Answer: 0 Posted On: 14-Feb-2016 12:11 Share: 
|
|
Rahul M...
4822
Points
23
Posts
|
Hi Indradaman, Temp Tables: Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index, keys like normal tables. Local temp tables are only available to the SQL Server session or single user (connection) that created the tables. These are automatically deleted when the session has been closed. Local temporary table name is stared with # sign. CREATE TABLE #LocalTemp
(
UserID int,
Name varchar(50)
)
GO
insert into #LocalTemp values ( 1, 'xyz');
GO
Select * from #LocalTemp
CTE: CTE (Common Table Expressions) is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. These are created in memory rather than Tempdb database. You cannot create any index on it. It is started by using WITH statement. With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age
from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1
WHERE CTE1.Age > 60
ORDER BY CTE1.NAME
Table Variable: Table Variable acts like a variable and exists for a particular block of query execution. It gets dropped once it comes out of block code. This is also created in the Tempdb database but not the memory. It can be careated by using @. GO
DECLARE @MProduct TABLE
(
SNo INT IDENTITY(1,1),
ProductID INT,
Qty INT
)
--Insert data to Table variable @Product
INSERT INTO @MProduct(ProductID,Qty)
SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC
--Select data
Select * from @MProduct
--Next batch
GO
Select * from @MProduct--gives error in next batch
Posted On:
15-Feb-2016 03:50
|
