Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server / SQL Server 2008
Article

Common Table Expressions(CTE) in SQL SERVER 2008

Rate me:
Please Sign up or sign in to vote.
4.83/5 (69 votes)
7 Oct 2011CPOL3 min read 512.8K   88   20
Coding in SQL with Common Table Expressions (CTE)

Introduction

It’s a headache for developers to write or read a complex SQL query using a number of Joins. Complex SQL statements can be made easier to understand and maintainable in the form of CTE or Common Table expressions. In this post, I will try to cover some of the benefits that we get when working with CTE.

When dealing with sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case, either you have an option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE 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. CTE is standard ANSI SQL standard.

Using the Code

Say, for instance, you have a query like this:

SQL
SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

The query looks really a mess. Even if I need to write something that wraps around the entire query, it would gradually become unreadable. CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output.

Rewriting the query using CTE expressions would look like:

SQL
With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

Yes as you can see, the second query is much more readable using CTE. You can specify as many query expressions as you want and the final query which will output the data to the external environment will eventually get reference to all of them.

SQL
With T1(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
),
T2(Name, Desig)
AS
(
SELECT NAME, DESIG FROM Designation)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME

So the queries are separated using commas. So basically you can pass as many queries as you want and these queries will act as a subqueries, getting you the data and name it as a temporary table in the query.

According to the syntax, the CTE starts with a With clause. You can specify the column names in braces, but it is not mandatory.

Common Table Expression Syntax

A Common Table Expression contains three core parts:

  • The CTE name (this is what follows the WITH keyword)
  • The column list (optional)
  • The query (appears within parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE.

When to Use Common Table Expressions

Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed, it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

  • Create a recursive query.
  • 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.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Using scalar subqueries (such as the (SELECT COUNT(1) FROM ...) examples we've looked at in this article) cannot be grouped or filtered directly in the containing query. Similarly, when using SQL Server 2005's ranking functions - ROW_NUMBER(), RANK(), DENSE_RANK(), and so on - the containing query cannot include a filter or grouping expression to return only a subset of the ranked results. For both of these instances, CTEs are quite handy.

CTEs can also be used to recursively enumerate hierarchical data.

Points of Interest

See the awful series with CTE:

SQL
WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST('I Like ' AS VARCHAR(300)), LEN('I Like ')
UNION ALL
SELECT
      CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))
      , LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage

So this will produce like this:

image001.jpg

History

  • 6th October, 2011: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) NeST IT
India India
I love to remain updated in technology. In spare times I explore with google, find new projects and solutions. My favourites is Asp.net, SQL server , javascript, WPF and Silverlight. I started my software job as a VB6 programmer.

My family includes husband(my great support),our baby son.

Comments and Discussions

 
PraiseCTE article - excellent Pin
Ed Madigan8-Mar-17 18:38
Ed Madigan8-Mar-17 18:38 
Questionnice article in simplified way Pin
Member 1260228024-Jun-16 4:18
Member 1260228024-Jun-16 4:18 
QuestionCommon Table Expressions(CTE) in SQL SERVER 2008 Pin
Nguyen Tran Ngoc Trong24-Jun-15 16:47
professionalNguyen Tran Ngoc Trong24-Jun-15 16:47 
GeneralGood one Pin
Bitla Phanindra19-Nov-14 0:09
Bitla Phanindra19-Nov-14 0:09 
GeneralAnother sample to generate tree using recursively CTE Pin
jigneshp.hip15-Aug-14 20:39
professionaljigneshp.hip15-Aug-14 20:39 
QuestionNice Explanation Pin
Sibeesh Venu28-Jul-14 1:18
professionalSibeesh Venu28-Jul-14 1:18 
GeneralMy vote of 5 Pin
Pratik Bhuva17-Jun-14 5:09
professionalPratik Bhuva17-Jun-14 5:09 
GeneralMy vote of 5 Pin
ThatsAlok4-Dec-13 2:41
ThatsAlok4-Dec-13 2:41 
GeneralGood job Sunitha.. Pin
Shalu34511-Oct-13 3:21
Shalu34511-Oct-13 3:21 
Questioncte Pin
rajacsharp511-Oct-13 3:04
rajacsharp511-Oct-13 3:04 
Questionregarding cte block Pin
shyam_d118-Aug-13 4:58
shyam_d118-Aug-13 4:58 
QuestionCTE is a big load on the server? Pin
RAND 4558669-Aug-13 8:00
RAND 4558669-Aug-13 8:00 
GeneralMy vote of 4 Pin
er. Mahesh Nagar14-Jun-13 1:04
er. Mahesh Nagar14-Jun-13 1:04 
GeneralMy vote of 5 Pin
KiranKumar Roy28-Jan-13 20:51
KiranKumar Roy28-Jan-13 20:51 
GeneralRe: My vote of 5 Pin
milind000913-May-13 2:03
milind000913-May-13 2:03 
QuestionError in Script Pin
Menahem Shcolnick17-Oct-12 11:31
Menahem Shcolnick17-Oct-12 11:31 
AnswerRe: Error in Script Pin
Sunitha Sudheesh18-Mar-14 2:30
Sunitha Sudheesh18-Mar-14 2:30 
GeneralMy vote of 4 Pin
ranganath um6-Oct-12 3:15
ranganath um6-Oct-12 3:15 
Questionsql objects Pin
Sunitha Sudheesh8-Oct-11 1:33
Sunitha Sudheesh8-Oct-11 1:33 
GeneralMy vote of 5 Pin
Ganu Sharma7-Oct-11 19:45
Ganu Sharma7-Oct-11 19:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.