Click here to Skip to main content
11,640,445 members (62,114 online)
Click here to Skip to main content

Common Table Expressions(CTE) in SQL SERVER 2008

, 7 Oct 2011 CPOL 273.3K 74
Rate this:
Please Sign up or sign in to vote.
Coding in SQL with Common Table Expressions (CTE)


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:

        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50

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:

With T(Address, Name, Age)  --Column names for Temporary table
SELECT A.Address, E.Name, E.Age from Address A
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50

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.

With T1(Address, Name, Age)  --Column names for Temporary table
SELECT A.Address, E.Name, E.Age from Address A
T2(Name, Desig)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.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:

      CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))
      , LEN(STATEMENT) FROM ShowMessage

So this will produce like this:



  • 6th October, 2011: Initial version


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


About the Author

Sunitha Sudheesh
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, 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.

You may also be interested in...

Comments and Discussions

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
memberBitla Phanindra19-Nov-14 0:09 
GeneralAnother sample to generate tree using recursively CTE Pin
jigneshp.hip15-Aug-14 20:39
memberjigneshp.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
memberThatsAlok4-Dec-13 2:41 
GeneralGood job Sunitha.. Pin
Shalu34511-Oct-13 3:21
memberShalu34511-Oct-13 3:21 
Questioncte Pin
rajacsharp511-Oct-13 3:04
memberrajacsharp511-Oct-13 3:04 
Questionregarding cte block Pin
shyam_d118-Aug-13 4:58
membershyam_d118-Aug-13 4:58 
QuestionCTE is a big load on the server? Pin
Member 4558669-Aug-13 8:00
memberMember 4558669-Aug-13 8:00 
GeneralMy vote of 4 Pin
er. MAhesh NAgar14-Jun-13 1:04
memberer. MAhesh NAgar14-Jun-13 1:04 
GeneralMy vote of 5 Pin
KiranKumar Roy28-Jan-13 20:51
memberKiranKumar Roy28-Jan-13 20:51 
GeneralRe: My vote of 5 Pin
milind000913-May-13 2:03
membermilind000913-May-13 2:03 
QuestionError in Script Pin
Menahem Shcolnick17-Oct-12 11:31
memberMenahem Shcolnick17-Oct-12 11:31 
AnswerRe: Error in Script Pin
Sunitha Sudheesh18-Mar-14 2:30
memberSunitha Sudheesh18-Mar-14 2:30 
GeneralMy vote of 4 Pin
ranganath um6-Oct-12 3:15
memberranganath um6-Oct-12 3:15 
Questionsql objects Pin
Sunitha Sudheesh8-Oct-11 1:33
memberSunitha Sudheesh8-Oct-11 1:33 
Microsoft SQL Server provides objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. An object is any SQL Server resource, such as a SQL Server lock or Windows XP process. Each object contains one or more counters that determine various aspects of the objects to monitor. For example, the SQL Server Locks object contains counters called Number of Deadlocks/sec and Lock Timeouts/sec.

I don't know much, I should explore it

GeneralMy vote of 5 Pin
Ganu Sharma7-Oct-11 19:45
groupGanu Sharma7-Oct-11 19:45 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 7 Oct 2011
Article Copyright 2011 by Sunitha Sudheesh
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid