When to use Common Table Expression CTE

Question

I have begun reading about Common Table Expression and cannot think of a use case where I would need to use them  They would seem to be redundant as the same can be done with derived tables  Is there something I am missing or not understanding well  Can someone give me a simple example of limitations with regular select  derived or temp table queries to make the case of CTE  Any simple examples would be highly appreciated

User · Answer

Today we are going to learn about Common table expression that is a new feature which was introduced in SQL server 2005 and available in later versions as well.

Common table Expression :- Common table expression can be defined as a temporary result set or in other words its a substitute of views in SQL Server. Common table expression is only valid in the batch of statement where it was defined and cannot be used in other sessions.

Syntax of declaring CTE(Common table expression) :-

with [Name of CTE]
as
(
Body of common table expression
)

Lets take an example :-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

I have created two tables employee and Dept and inserted 5 rows in each table. Now I would like to join these tables and create a temporary result set to use it further.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Lets take each line of the statement one by one and understand.

To define CTE we write "with" clause, then we give a name to the table expression, here I have given name as "CTE_Example"

Then we write "As" and enclose our code in two brackets (---), we can join multiple tables in the enclosed brackets.

In the last line, I have used "Select * from CTE_Example" , we are referring the Common table expression in the last line of code, So we can say that Its like a view, where we are defining and using the view in a single batch and CTE is not stored in the database as an permanent object. But it behaves like a view. we can perform delete and update statement on CTE and that will have direct impact on the referenced table those are being used in CTE. Lets take an example to understand this fact.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

In the above statement we are deleting a row from CTE_Example and it will delete the data from the referenced table "DEPT" that is being used in the CTE.

User · Answer

It is very useful when you want to perform an  ordered update    MS SQL does not allow you to use ORDER BY with UPDATE  but with help of CTE you can do it that way   WITH cte AS       SELECT TOP 5000  message compressed  message  exception compressed  exception     FROM logs     WHERE Id  gt   5519694      ORDER BY Id   UPDATE  cte SET     message compressed   COMPRESS message   exception compressed   COMPRESS exception    Look here for more info  How to update and order by using ms sql

User · Answer

One example  if you need to reference join the same data set multiple times you can do so by defining a CTE  Therefore  it can be a form of code re-use   An example of self referencing is recursion  Recursive Queries Using CTE  For exciting Microsoft definitions Taken from Books Online   A CTE can be used to    Create a recursive query  For more information  see Recursive Queries Using Common Table Expressions  Substitute for a view when the general use of a view is not required  that is  you do not have to store the definition in metadata  Enable grouping by a column that is derived from a scalar subselect  or a function that is either not deterministic or has external access  Reference the resulting table multiple times in the same statement

User · Answer

Perhaps its more meaningful to think of a CTE as a substitute for a view used for a single query  But doesn t require the overhead  metadata  or persistence of a formal view  Very useful when you need to    Create a recursive query   Use the CTE s resultset more than once in your query  Promote clarity in your query by reducing large chunks of identical subqueries  Enable grouping by a column derived in the CTE s resultset   Here s a cut-and-paste example to play with   WITH  cte example  AS   SELECT 1 AS  myNum    a num  as  label  UNION ALL SELECT  myNum  1  label  FROM  cte example  WHERE  myNum   lt    10   SELECT   FROM  cte example  UNION SELECT SUM  myNum     sum all  FROM  cte example  UNION SELECT SUM  myNum     sum odd  FROM  cte example  WHERE  myNum    2   1 UNION SELECT SUM  myNum     sum even  FROM  cte example  WHERE  myNum    2   0    Enjoy

User · Answer

One point not pointed out yet  is the speed  I know it s an old answered question  but I think this deserves direct comment answer      They would seem to be redundant as the same can be done with derived tables   When I used CTE the very first time I was absolutely stunned by it s speed  It was a case like from a textbook  very suitable for CTE  but in all ocurences I ever used CTE  there was a significant speed gain  My first query was complex with derived tables  taking long minutes to execute  With CTE it took fractions of seconds and left me shocked  that it is even possible

User · Answer

One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table  With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood  it will be unreadable and cause performance issue   But by using CTE  as answered by Tim Schmelter on Select the first instance of a record   WITH CTE AS      SELECT myTable         RN   ROW NUMBER  OVER PARTITION BY patientID ORDER BY ID      FROM myTable    SELECT   FROM CTE WHERE RN   1   As you can see  this is much easier to read and maintain  And in comparison to other queries  is much better at performance

User · Answer

with cte as       Select Department  Max salary  as MaxSalary   from test   group by department         select t   from test t join cte c on c department t department    where t salary c MaxSalary    try this

User · Answer

I use them to break up complex queries  especially complex joins and sub-queries  I find I m using them more and more as  pseudo-views  to help me get my head around the intent of the query   My only complaint about them is they cannot be re-used  For example  I may have a stored proc with two update statements that could use the same CTE  But the  scope  of the CTE is the first query only   Trouble is   simple examples  probably don t really need CTE s   Still  very handy

User · Answer

There are two reasons I see to use cte s    To use a calculated value in the where clause  This seems a little cleaner to me than a derived table    Suppose there are two tables - Questions and Answers joined together by Questions ID   Answers Question Id  and quiz id   WITH CTE AS       Select Question Text              SELECT Count    FROM Answers A WHERE A Question ID   Q ID  AS Number Of Answers     FROM Questions Q   SELECT   FROM CTE WHERE Number Of Answers  gt  0   Here s another example where I want to get a list of questions and answers  I want the Answers to be grouped with the questions in the results    WITH cte AS       SELECT  Quiz ID           ID  AS Question Id        null AS Answer Id             Question Text             null AS Answer            1 AS Is Question     FROM  Questions       UNION ALL      SELECT Q  Quiz ID          Question ID         A  ID  AS  Answer Id        Q Question Text             Answer             0 AS Is Question         FROM  Answers  A INNER JOIN  Questions  Q ON Q Quiz ID   A Quiz ID AND Q Id   A Question Id   SELECT      Quiz Id      Question Id      Is Question       CASE WHEN Answer IS NULL THEN Question Text ELSE Answer END  as Name FROM cte     GROUP BY Quiz Id  Question Id  Answer id  Question Text  Answer  Is Question  order by Quiz Id  Question Id  Is Question Desc  Name

[sql] When to use Common Table Expression (CTE)

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to common-table-expression