What is CTE? and Difference between CTE,TableVariable and Temptable..
Here we discuss about Common Table Expressions(CTE) and Difference between CTE,TableVariable and Temptable.
There are so many differences between them, let me explain some of that differences with examples. Let us see...
Common Table Expressions(CTE):
CTE is stands for Common Table Expressions that allows you to define the subquery at once
name it using an alias and later call the same data using the alias just like what you do
with a normal table.
It is a ANSI SQL standard.
If we are having 6 columns in a table like ID,A,B,C,D,AMOUNT
our task is add A+B+C+D and then do arithmetic operations with amount means
every time we need to add all a,b,c,d
like below
SELECT ID,(A+B+C+D)/AMOUNT AS DIVIDE,(A+B+C+D)*AMOUNT AS MULTIPLY,
(A+B+C+D)-AMOUNT AS SUB,(A+B+C+D)+AMOUNT AS ADD FROM YOURTABLE
Instead of that we write like this...
WITH CTE1 AS
(SELECT ID,A+B+C+D AS COLUMNVALUE FROM YOURTABLE)
SELECT A.COLUMNVALUE/B.AMOUNT AS DIVIDE,
A.COLUMNVALUE*B.AMOUNT AS MULTIPLY,A.COLUMNVALUE-B.AMOUNT AS SUB,A.COLUMNVALUE+B.AMOUNT AS ADD FROM CTE1 A
INNER JOIN YOURTABLE B ON A.ID=B.ID
Difference between CTE,TableVariable and Temptable..
Temp tables tables are physically created in the Tempdb database and it act as like a normal tables we can have index,constraints etc.
But CTE is not like that it just a named temporary result set which is used to manipulate the complex sub-queries data exists for the scope of statement.
we cannot create any constraints or index in cte..
CTE is created in memory rather than Tempdb database
If you do any update or delete operations in cte it will affect the main table..
Eg:-
CREATE TABLE UserTable(UserID VARCHAR(5))
INSERT INTO UserTable(UserID) VALUES('UR001'),('UR002'),('UR003'),('UR004'),('UR005')
WITH CTE AS
(SELECT USERID FROM USERTABLE)
SELECT * FROM CTE
;WITH CTE AS
(SELECT USERID FROM USERTABLE)
UPDATE CTE SET USERID='A' WHERE USERID='UR001'
SELECT * FROM CTE--It shows "Invalid object name 'CTE'"
SELECT * FROM USERTABLE
'A' is directly affected with 'UR001' in main table..
;WITH CTE AS
(SELECT USERID FROM USERTABLE)
DELETE FROM CTE where UserID='A'
SELECT * FROM USERTABLE
'A' is deleted from our main table..
Table variable is acts like a variable which exists for a particular batch of query execution.
It gets dropped once it comes out of batch,this is also created in the Tempdb database but not the memory.
Whereas CTE is not affected tempdb at all!
The below example may clear your doubt easily...
USE Adventureworks
CREATE TABLE UserTable(UserID VARCHAR(5),UserName VARCHAR(10),Amount MONEY)
INSERT INTO UserTable(UserID,UserName,Amount) VALUES('UR001','A',500),('UR002','B',600),
('UR003','C',700),('UR004','D',800),('UR005','E',900)
--***********************************************************************************
First we check temp tables
USE TempDB;
GO
CHECKPOINT;
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
USE Adventureworks;
CREATE TABLE #Temp(UserID VARCHAR(5),UserName VARCHAR(10),Amount VARCHAR)
INSERT INTO #Temp SELECT * FROM UserTable
SELECT * FROM #Temp
DROP TABLE #Temp
GO
USE TempDB;
GO
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
Output:
NumberOfLogEntries TotallogrecordLength
------------- ------------------
2 232
UserID UserName Amount
------ ---------- ------
UR001 A 500
UR002 B 600
UR003 C 700
UR004 D 800
UR005 E 900
NumberOfLogEntries TotallogrecordLength
------------- ------------------
89 10396
see the differnce before we use tempdb and after..
--***********************************************************************************
Now we check Table Variable...
USE TempDB;
GO
CHECKPOINT;
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
USE Adventureworks;
DECLARE @Tablevariable TABLE (UserID VARCHAR(5),UserName VARCHAR(10),Amount VARCHAR)
INSERT INTO @Tablevariable SELECT * FROM UserTable
SELECT * FROM @Tablevariable
GO
USE TempDB;
GO
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
Output:
NumberOfLogEntries TotallogrecordLength
------------- ------------------
2 232
UserID UserName Amount
------ ---------- ------
UR001 A 500
UR002 B 600
UR003 C 700
UR004 D 800
UR005 E 900
NumberOfLogEntries TotallogrecordLength
------------- ------------------
55 5704
see the differnce before we use tempdb and after..
--***********************************************************************************
Now we check CTE..
USE TempDB;
GO
CHECKPOINT;
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
USE Adventureworks;
WITH CTE AS(
SELECT * FROM UserTable)
SELECT * FROM UserTable
USE TempDB;
GO
SELECT COUNT(Operation) as NumberOfLogEntries,SUM("log record length") AS TotallogrecordLength FROM fn_dblog(null, null);
Output:
NumberOfLogEntries TotallogrecordLength
------------- ------------------
2 232
UserID UserName Amount
------ ---------- ------
UR001 A 500
UR002 B 600
UR003 C 700
UR004 D 800
UR005 E 900
NumberOfLogEntries TotallogrecordLength
------------- ------------------
2 232
There is no difference berfore and after because it doesnot affects tempdb..
--***********************************************************************************
Hope it will helpful to you!!
Regards,
Sri