Click here to Skip to main content
12,997,531 members (60,026 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 19 Jan 2014

CTE Query in SQL Server

, 20 Jan 2014
Rate this:
Please Sign up or sign in to vote.
CTE Query for optimizing complexity of query in sql server


Common Table Expressions (CTE) query is very useful to reduce query length as well complexity.

Generally we are using Joins for retrieving records from multiple tables, It is difficult and complex little bit than CTE queries.

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE query returns us a single query from multiple joins query so we can easily deal with it and get more expected result in less effort.

Using the code

It is very easy to write CTE query.

A Common Table Expression defines with three components.

  1. CTE name after WITH phrase
  2. Column list (It is not mandatory)
  3. Final query (appears within parentheses after the AS keyword)

Let see Format of CTE query

WITH CTEname(Columnlist optional) AS(..sql query..)

it returns all columns of sql query which has been written in parenthesis..


WITH SampleData AS(SELECT e1.column2 AS EmpName,e2.column2 AS Department FROM table1 e1 INNER JOIN table2 e2 ON e1.Column3=e2.column1) 
select * from SampleData  

it returns two columns having name EmpName and Department and it act like single and simple query.

We can use multiple CTE query as well

with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)
    ,samp1 as(
            select * from samp
            select 'Code' as Text1 ,'project' as Text2
select * from samp1  

we can define column name as well like this.

WITH CTEname (columnlist) as (....) 
 with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)

    ,samp1(MyColumn1,MyColumn2) as(
            select * from samp
            select 'Code' as Text1 ,'project' as Text2
select * from samp1  

This query returns output of both queries.. 

Points of Interest

I have retrieved dates between date interval from first date to second date like this..

			  SELECT CAST('23 Mar 2014' AS DATETIME) AS dt
			  SELECT DATEADD(dd, 1, dt)
			   FROM CTEQuery s
			   WHERE DATEADD(dd, 1, dt) <= CAST('26 Jun 2014' AS DATETIME)
select * from CTEQuery 

It returns all the date row wise between 23 Mar 2014 to 26 Jun 2014.... :)


  • 20 Jan 2014, initial level.. 


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


About the Author

Nirav Prabtani
Team Leader CR2 Technologies LTD
India India

Nirav Prabtani

I am a team lead, Database Architect and Designer /Technical Architect/Analyst,
Programmer in Microsoft .NET Technologies & Microsoft SQL Server with more than
4.5 years of hands on experience.

I love to code....!!! Smile | :)

My recent past includes my work with the education domain as a technical business
requirement analyst, database architect & designer and analyst programmer; just
love my involvement with the world of knowledge, learning and education and I think
I know quite well what I want to do in life & in my career. What do I like? Well,
ideation, brainstorming, coming up with newer and more creative ways of doing things;
each time with an enhanced efficiency. An item in my day's agenda always has a task
to look at what I did yesterday & focus on how I can do it better today

Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email :

My Blog:
Nirav Prabtani

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170622.1 | Last Updated 20 Jan 2014
Article Copyright 2014 by Nirav Prabtani
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid